Skip to main content
February 12, 2024
Question

How can I add a filter(Countofvehicles]10) for aggregate field for below code?

  • February 12, 2024
  • 5 replies
  • 0 views

/*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"
    )  
  )
)

    5 replies

    venkatrea696188
    February 12, 2024

    Can you elaborate a bit more about what you wanna achieve

    February 12, 2024

    Thanks for giving response [mention:5abb751204a145abaeb28c7bf620b9d5:e9ed411860ed4f2ba0265705b8793d05] 


    above code is for grouping the "color" field and counting how many vehicles per each color. the output like for example 
    color     countofvechiles
    RED       20
    WHITE   10
    BLUE      5
    BLACK   3

    Now , I want to extend that code to add a filter of aggregate filed  "countofvehicles"  like, I want to display only  countofvehicles>5 , not all

    harshitb6843
    February 12, 2024

    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. 

    stefanhelzle0001
    February 12, 2024

    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