How to merge lines and calculate average data on a CDT

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

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...

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

Ok I understand, thank you for the explanation.

But your previous code will be perfect for what I need.

Danny, this time how would you do the Qty sum on 2 disctinct field values (Type1 et Type2) ?  (without any SQL of course)

a!localVariables(
  local!myData: {
    a!map(id: 1, type1: 1, type2: 12, value: 10),
    a!map(id: 2, type1: 1, type2: 12, value: 15),
    a!map(id: 3, type1: 1, type2: 25, value: 20),
    a!map(id: 4, type1: 1, type2: 12, value: 25),
    a!map(id: 5, type1: 2, type2: 30, value: 5),
    a!map(id: 6, type1: 2, type2: 30, value: 15),
    a!map(id: 7, type1: 2, type2: 12, value: 15),
    a!map(id: 8, type1: 2, type2: 12, value: 22)
  },
  local!uniqueTypesParent: union(local!myData.type1, local!myData.type1),
  local!aggregateValues: a!flatten(
    a!forEach(
      items: local!uniqueTypesParent,
      expression: a!localVariables(
        local!parent: fv!item,
        local!subsetData: index(
          local!myData,
          wherecontains(
            tointeger(local!parent),
            tointeger(local!myData.type1)
          ),
          {}
        ),
        local!uniqueTypesChild: union(
          local!subsetData.type2,
          local!subsetData.type2
        ),
        a!forEach(
          items: local!uniqueTypesChild,
          expression: a!map(
            id: fv!index,
            type1: local!parent,
            type2: fv!item,
            value: sum(
              index(
                local!subsetData.value,
                wherecontains(
                  tointeger(fv!item),
                  tointeger(local!subsetData.type2)
                ),
                {}
              )
            )
          )
        )
      )
    )
  ),
  a!update(
    data: local!aggregateValues,
    index: "id",
    value: 1 + enumerate(length(local!aggregateValues))
  )
)

Thanks a lot Siddharth, but it seems the index is incorrect.

Your code generates : 1,2,1,2,1,2  (but 1,2,3,4,5,6 expected)

It does generate the correct index values (the a!update at line 51 has been used for generating the correct 'id' values). Can you please double check.

Thank you Siddharth,

Sorry, the index is not a problem, and I finally need more to get the id (or list of ids) than the index.

I've created another example with an addional level (I have now : Type, SubType ans SubType2).

All seems to work well but I have met a special case.

Here is the example and code below.

Could you tell me why I am obtaining this 5th line please ? (here is my code below)

  local!aggregateValues: a!flatten(
    a!forEach(
      items: local!uniqueTypesParent,
      expression: a!localVariables(
        local!parent: fv!item,
        local!subsetData: index(
          local!vehicles,
          wherecontains(
            tointeger(local!parent),
            tointeger(local!vehicles.typeid)
          ),
          {}
        ),
        local!uniqueSubTypesChild: union(
          local!subsetData.subtypeid,
          local!subsetData.subtypeid
        ),
        a!forEach(
          items: local!uniqueSubTypesChild,
          expression: a!localVariables(
            local!parent1: fv!item,
            local!subsetData1: index(
              local!vehicles,
              wherecontains(
                tointeger(local!parent1),
                tointeger(local!vehicles.subtypeid)
              ),
              {}
            ),
            local!uniqueSubTypesChild2: union(
              local!subsetData1.subtypeid2,
              local!subsetData1.subtypeid2
            ),
            a!forEach(
              items: local!uniqueSubTypesChild2,
              expression: a!map(
                id: a!localVariables(
                  local!listeTypes: index(
                    local!vehicles,
                    wherecontains(local!parent, local!vehicles.typeid)
                  ),
                  local!listeSubTypes: index(
                    local!listeTypes,
                    wherecontains(local!parent1, local!listeTypes.subtypeid)
                  ),
                  local!listeSubTypes2: index(
                    local!listeSubTypes,
                    wherecontains(fv!item, local!listeSubTypes.subtypeid2)
                  ),
                  local!listeSubTypes2.id
                ),
                typeid: local!parent,
                subtypeid: local!parent1,
                subtypeid2: fv!item,
                value: sum(
                  index(
                    local!subsetData.value,
                    wherecontains(
                      tointeger(fv!item),
                      tointeger(local!subsetData.subtypeid2)
                    ),
                    {}
                  )
                )
              )
            )
          )
        )
      )
    )
  ), 

no idea for my last example ?

This is because you are not filtering out the data properly. The index operation in line 22 should be used on local!subsetData and not local!vehicles.

index(
  local!subsetData,
  wherecontains(
    tointeger(local!parent1),
    tointeger(local!subsetData.subtypeid)
  ),
  {}
)

Thanks a lot Siddharth, I've missed this detail...

Thanks a lot Siddharth, I've missed this detail...