• State Suggested Answer
  • Replies 19 replies
  • Answers 2 answers
  • Subscribers 14 subscribers
  • Views 15247 views
  • Users 0 members are here
Related Discussions
Home » Discussions » User Interface

Need help on making aggregation on sail code with filter

sauravk

Hi,

 

I want to perform some group by operation on sail code on required dataset with some filters.

 

Do we have any way to do that on sail code not on expression rules ?

 

Actually i have received bulk data set from one view and i want to use one list of one field value to traverse whole dataset, so just fo that details i don't want to hit database again, for that i want to do group on existing dataset only, so if we have any option for grouping on sail code plz suggest.

 

Regards

Saurav

  Discussion posts and replies are publicly visible

Parents
  • 0
    A Score Level 1
    As per my understanding you want to aggregate data at sail code.
    please find the below code snippet showcasing the same

    load(
    local!data: {
    {
    id: 1,
    code: "SOUTHERN"
    },
    {
    id: 2,
    code: "SOUTHERN"
    },
    {
    id: 3,
    code: "NORTHERN"
    },
    {
    id: 4,
    code: "NORTHERN"
    },
    {
    id: 5,
    code: "NORTHERN"
    }
    },
    a!forEach(
    items: union(
    index(
    local!data,
    "code",
    {}
    ),
    index(
    local!data,
    "code",
    {}
    )
    ), /* use the filter() function for your further requirement */
    expression: {
    code: fv!item,
    count: length(
    wherecontains(
    fv!item,
    touniformstring(
    index(
    local!data,
    "code",
    {}
    )
    )
    )
    )
    }
    )
    )

    regards,
    Sachin
  • 0 in reply to Sachin
    I want to perform something like this .

    a!queryEntity(
    entity: cons!TEST_VIEW,
    query: a!query(
    aggregation: a!queryAggregation(
    aggregationColumns: {
    a!queryAggregationColumn(
    field: "testName",
    isGrouping: true
    ),
    a!queryAggregationColumn(
    field: "test1",
    alias: "test1",
    aggregationFunction: "SUM"
    ),
    a!queryAggregationColumn(
    field: "test2",
    alias: "test2",
    aggregationFunction: "SUM"
    ),
    a!queryAggregationColumn(
    field: "test3",
    alias: "test3",
    aggregationFunction: "SUM"
    ),
    a!queryAggregationColumn(
    field: "test4",
    alias: "test4",
    aggregationFunction: "SUM"
    )
    }
    ),
    logicalExpression: a!queryLogicalExpression(
    operator: "AND",
    filters: {
    a!queryFilter(
    field: "testName",
    operator: "not in",
    value: {
    "A",
    "B"
    }
    ),
    if(
    or(
    isusermemberofgroup(
    loggedInUser(),
    cons!TEST_GROUP1
    ),
    isusermemberofgroup(
    loggedInUser(),
    cons!TEST_GROUP2
    ),
    isusermemberofgroup(
    loggedInUser(),
    cons!TEST_GROUP3
    ),
    isusermemberofgroup(
    loggedInUser(),
    cons!TEST_GROUP4
    )
    ),
    {},
    a!queryFilter(
    field: "testName",
    operator: "not in",
    value: cons!C
    )
    )
    }
    ),

    pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: - 1,
    sort: a!sortInfo(
    field: "testName",
    ascending: true
    )
    )
    )
    )
  • 0
    A Score Level 1
     in reply to sauravk

    for filtering i tried to replicated the above query which you have provided

     

     

    /*Filtered rule*/
and(
  not(
    contains(
      {
        "A",
        "B"
      },
      touniformstring(
        index(
          ri!yourCdt,
          "testName",
          null()
        )
      )
    )
  ),
  if(
    or(
      isusermemberofgroup(
      loggedInUser(),
      cons!TEST_GROUP1
      ),
      isusermemberofgroup(
      loggedInUser(),
      cons!TEST_GROUP2
      ),
      isusermemberofgroup(
      loggedInUser(),
      cons!TEST_GROUP3
      ),
      isusermemberofgroup(
      loggedInUser(),
      cons!TEST_GROUP4
      )
    ),
    true(),
    not(
      contains(
        "your value",
        index(
          ri!yourCdt,
          "testName",
          null()
        )
      )
    )
  )
)

Main rule
load(
  local!data: {
    {
      id: 1,
      code: "SOUTHERN",
      test1: 10,
      test2: 40,
	  testName: "A"
    },
    {
      id: 2,
      code: "SOUTHERN",
      test1: 10,
      test2: 60,
	  testName: "A"
    },
    {
      id: 3,
      code: "NORTHERN",
      test1: 10,
      test2: 25,
	  testName: "B"
    },
    {
      id: 4,
      code: "NORTHERN",
      test1: 10,
      test2: 5,
	  testName: "AA"
    },
    {
      id: 5,
      code: "NORTHERN",
      test1: 10,
      test2: 10,
	  testName: "AB"
    }
  },
  local!filterData:filter(rule!SAC_filterRule(_),ri!dictionary),
  a!forEach(
    items: union(
      index(
        local!filterData,
        "code",
        {}
      ),
      index(
        local!filterData,
        "code",
        {}
      )
    ),     
    expression: with(
      local!currentDetails: index(
        local!filterData,
        wherecontains(
          fv!item,
          touniformstring(
            index(
              local!filterData,
              "code",
              {}
            )
          )
        ),
        null()
      ),
      {
        code: fv!item,
        test1: sum(
          index(
            local!currentDetails,
            "test1",
            null()
          )
        ),
        test2:sum(
          index(
            local!currentDetails,
            "test2",
            null()
          )
        )
      }
    )
  )
)

Reply
  • 0
    A Score Level 1
     in reply to sauravk

    for filtering i tried to replicated the above query which you have provided

     

     

    /*Filtered rule*/
and(
  not(
    contains(
      {
        "A",
        "B"
      },
      touniformstring(
        index(
          ri!yourCdt,
          "testName",
          null()
        )
      )
    )
  ),
  if(
    or(
      isusermemberofgroup(
      loggedInUser(),
      cons!TEST_GROUP1
      ),
      isusermemberofgroup(
      loggedInUser(),
      cons!TEST_GROUP2
      ),
      isusermemberofgroup(
      loggedInUser(),
      cons!TEST_GROUP3
      ),
      isusermemberofgroup(
      loggedInUser(),
      cons!TEST_GROUP4
      )
    ),
    true(),
    not(
      contains(
        "your value",
        index(
          ri!yourCdt,
          "testName",
          null()
        )
      )
    )
  )
)

Main rule
load(
  local!data: {
    {
      id: 1,
      code: "SOUTHERN",
      test1: 10,
      test2: 40,
	  testName: "A"
    },
    {
      id: 2,
      code: "SOUTHERN",
      test1: 10,
      test2: 60,
	  testName: "A"
    },
    {
      id: 3,
      code: "NORTHERN",
      test1: 10,
      test2: 25,
	  testName: "B"
    },
    {
      id: 4,
      code: "NORTHERN",
      test1: 10,
      test2: 5,
	  testName: "AA"
    },
    {
      id: 5,
      code: "NORTHERN",
      test1: 10,
      test2: 10,
	  testName: "AB"
    }
  },
  local!filterData:filter(rule!SAC_filterRule(_),ri!dictionary),
  a!forEach(
    items: union(
      index(
        local!filterData,
        "code",
        {}
      ),
      index(
        local!filterData,
        "code",
        {}
      )
    ),     
    expression: with(
      local!currentDetails: index(
        local!filterData,
        wherecontains(
          fv!item,
          touniformstring(
            index(
              local!filterData,
              "code",
              {}
            )
          )
        ),
        null()
      ),
      {
        code: fv!item,
        test1: sum(
          index(
            local!currentDetails,
            "test1",
            null()
          )
        ),
        test2:sum(
          index(
            local!currentDetails,
            "test2",
            null()
          )
        )
      }
    )
  )
)

Children
No Data
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.