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.
Discussion posts and replies are publicly visible
Hi,
The best way is to create a new view and add all the business logic inside the view itself. and use query entity to fetch all the details from the view.
Thanks
Vinay
Hi Vinay,
Thanks. So i have to go to Database and under Views , i have to create a new one?
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) ) ) )
Ankita,
Wow! that's exactly what i am looking for. i went with the load query you gave.
Thank you so much!
That's great! Could you please verify my answer if it helped you.
Thanks :)
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 ), {} ) )