Skip to main content
C
cedricj425261
August 27, 2021
Question

How to merge lines and calculate average data on a CDT

  • August 27, 2021
  • 12 replies
  • 0 views

Hi,

I have a CDT that contains many lines. How would you construct a new CDT (new local variable) with merged lines (with average calculations) depending of a field values ? (like the picture below) 

Naively, I would use a foreach and create some lists, but I'm sure Appian provides some great functions to make this stuff easier :-)

(the question is not SQL-query oriented, I'm looking the best way to do it with SAIL from an existing cdt)

Regards

12 replies

danny.verb
danny.verb
August 27, 2021

a!localVariables(
  local!myData: {
    a!map(
      id: 1,
      typeId: 1,
      value: 10
    ),
    a!map(
      id: 2,
      typeId: 1,
      value: 15
    ),
    a!map(
      id: 3,
      typeId: 2,
      value: 5
    ),
    a!map(
      id: 4,
      typeId: 2,
      value: 22
    )
  },
  local!uniqueTypes: union(local!myData.typeId,local!myData.typeId),
  local!aggregateValues: a!forEach(
    local!uniqueTypes,
    a!map(
      id: fv!index,
      typeId: fv!item,
      value: sum(index(
        local!myData.value,
        wherecontains(tointeger(fv!item),tointeger(local!myData.typeId)),
        {}
      ))
    )
  ),
  local!aggregateValues
)

The above code should work

C
cedricj425261Author
August 27, 2021

Thanks a lot Danny. I would probably have coded almost the same thing in 3 times more times... ;-)

but is there no other way to do it without a foreach ? with a native Appian function for example...

danny.verb
danny.verb
August 27, 2021

If your data is already in SAIL, then no there's no way to group and aggregate in the way you want. In the case I placed above, you're only looping through the unique values in your list and using the native sum() or average() functions on the corresponding values. 

If your data is in a database table then you can easily use a!queryEntity or a!queryRecordType to aggregate the data on query.

How is your data getting into your SAIL interface? If it's from a query, then simply update that query to use a queryAggregation

Terms & ConditionsAccessibility statement