Need help on making aggregation on sail code with filter

Yes you are correct. Otherwise no use to again hit DB for aggergation purpose ons ame set of data which we have already.

I can call query entiry rule also but i can't hit DB again for same set of data. If any possibility to perform aggregation on existing dataset by passing to query entity rule then i am fine but i am so new to this tool so don't know limitation of this tool.If you know something like that then plz share small sample code to me .

so what option you suggest ?

sauravk 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

I need to perform group by with sum function on my existing dataset.

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
)
)
)
)

You should use query entity with aggregation as has been suggested. However, in the interests of answering the question, here's some sample code that uses union(), a!forEach() and wherecontains() to do a group by and sum across a dictionary - hope it helps.

 

load(
  local!data: {
    {name: "snake",value: 54},
    {name: "badger",value: 40},
    {name: "badger",value: 60},
    {name: "mushroom",value: 70},
    {name: "snake",value: 49},
    {name: "mushroom",value: 50},
    {name: "snake",value: 9},
    {name: "mushroom",value: 34},
    {name: "badger",value: 75},
    {name: "snake",value: 23},
    {name: "mushroom",value: 85},
    {name: "mushroom",value: 23},
    {name: "mushroom",value: 85},
    {name: "snake",value: 23},
    {name: "badger",value: 1},
    {name: "snake",value: 45},
    {name: "badger",value: 36},
    {name: "mushroom",value: 64},
    {name: "badger",value: 32},
    {name: "badger",value: 84},
    {name: "mushroom",value: 99},
    {name: "snake",value: 13},
    {name: "snake",value: 23},
    {name: "snake",value: 13}
  },
  a!forEach(
    items: union(
      local!data.name,
      local!data.name
    ),
    expression: {
      name: fv!item,
      value: sum(
        index(
          local!data.value,
          wherecontains(
            fv!item,
            /* casting to string because local!data is a dictionary */
            /* and wherecontains() can't handle two different types */
            touniformstring(
              local!data.name
            )
          ),
          0
        )
      )
    }
  )
)

Edit: just noticed that this is basically the same as the code Sachin has posted, which is also correct :)

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()
          )
        )
      }
    )
  )
)

Hi Saurav,

If you are facing with the performance issue, try to apply indexing in the database. As if you will apply the indexing on the tables of which you have created the view, it will improve the performance of the view and query entity will retrieve the data fastly.