/*fetch the data group by colour and for each colour how many vechicles are there in that record type*/ a!queryRecordType( recordtype:recordType!AA Vehicle, fields:a!aggregationFields( groupings: { a!grouping( field:recordType!AA Vehicle.fields.color, alias:"color" ) }, measures: { a!measure( field:'recordType!AA Vehicle.fields.vehicleId', function:"COUNT", alias:"countofvehicles" ) } ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: 100, sort: a!sortInfo( field:"countofvehicles" ) ) )
Discussion posts and replies are publicly visible
Can you elaborate a bit more about what you wanna achieve
Thanks for giving response above code is for grouping the "color" field and counting how many vehicles per each color. the output like for example color countofvechilesRED 20WHITE 10BLUE 5BLACK 3Now , I want to extend that code to add a filter of aggregate filed "countofvehicles" like, I want to display only countofvehicles>5 , not all
In SQL code you would use a HAVING statement to do that. Appian does not support this.
Check out this documentation about what can be done with queries: https://docs.appian.com/suite/help/23.4/Query_Recipes.html
I don't think you can do it in the query directly. You will have to run a loop on the data received and check if fv!item.countofvehicles >5. If true, then return the fv!item, else return {}
Now because Appian doesn't print {} in the output, you will be left with the required data.
We can't apply Filters on Aggregated Fields Cause filters asks for record! references , you can go with the Method Harshit suggested Looping the results.