Filter dataSubset after aggregation

Question

Hi Team, 
 i am new to appian and i wanted to show frequently changing employees in a grid. 
 i have a queryentity which uses queryaggregationcolumns employeeid for groupby and transferid for count with alias name given. 
 once i get the result from the dataSubset i only wanted to display the rows with transferidcount>=3. 
 i am facing below issues - 1. i am not able to use queryfilter as alias name is not getting accepted here. 
 2. Filter is asking for list as second parameter 
 3.Load datasubset where(count>=3) is giving me intergers as output 
 4.sometimes i get list of boolean values as an output like if count is >=3 True and false for other rows. 
 
 i have gone through existing posts but couldnot make it out of them. 
 Kindly help.

kowsalyav · Accepted Answer

Ankita, 
 Wow! that's exactly what i am looking for. i went with the load query you gave. 
 Thank you so much!

Peter Lewis · Answer

I would recommend against doing a separate query. It will take at least twice the time to query multiple times (especially while using the IN operator). Instead, I'd recommend using a combination of index() and while() to filter the data using an expression: a!localVariables(
 local!empIds: a!queryEntity(
 entity: cons!Employee_Transfer_ENTITY,
 query: a!query(
 aggregation: a!queryAggregation(
 aggregationColumns: { 
 a!queryAggregationColumn(
 field: "employee_id",
 isGrouping :true()
 ),
 a!queryAggregationColumn(
 field: "transfer_id",
 alias: "count",
 aggregationFunction :"COUNT"
 )
 }
 ),
 pagingInfo: a!pagingInfo(1,-1)
 )
 ),
 local!filteredData: index(
 local!empIds.data,
 where(
 tointeger(local!query.data.count)>3
 ),
 {}
 )
)

ankitab0001 · Answer

Hi kowsalyav 
 
 There are 2 ways of achieving this: 
 1) Write a database query to group the employee id and create a view or use the query in the executequery() smart service to get the data directly from the table into appian 
 
 2) If you want to use the query aggregation feature, there's a two step query entity way 
 load(
 local!empIds: a!queryEntity(
 entity: cons!Employee_Transfer_ENTITY,
 query: a!query(
 aggregation: a!queryAggregation(
 aggregationColumns :{ a!queryAggregationColumn(
 field: "employee_id",
 isGrouping :true()
 ),
 a!queryAggregationColumn(
 field: "transfer_id",
 alias: "count",
 aggregationFunction :"COUNT"
 )
 }
 ),
 
 pagingInfo: a!pagingInfo(1,-1)
 )
),

local!requiredEmployees:a!forEach(local!empId,
if(tointeger(fv!item.count)>3,fv!item,{})),


a!queryEntity(
 entity: cons!Employee_Transfer_ENTITY,
 query: a!query(
 
 filter: a!queryFilter(
 field: "employee_id",
 operator: "in",
 value:tointeger(local!requiredEmployees.employee_id)
 ),
 pagingInfo: a!pagingInfo(1,-1)
 )
)
)

Filter dataSubset after aggregation

Top Replies