Foreach data grouping by value

Question

Hello, I have the following query: 
 
 Dictionary id1(Number (Integer)) currency"USD"(Text) createdBy"user1@gmail.com"(Text) value 1000 (Number (Decimal)) Dictionary id2(Number (Integer)) currency"USD"(Text) createdBy"user1@gmail.com"(Text) value 2000 (Number (Decimal)) Dictionary id3(Number (Integer)) currency"USD"(Text) createdBy"user2@gmail.com"(Text) value 2000 (Number (Decimal)) 
 
 Question is how to foreach this and the result to be grouped by createdBy in this case and the value to be a sum. 
 
 Example result: 
 
 createdby: user1@gmail.com 
 value: 3000 
 
 createdby: user2@gmail.com 
 value: 2000

Mike Schmitt · Accepted Answer

If this data is in your database, I believe you can use query aggregation functionality to group by value on a certain column and then do operations such as SUM on other column(s). 
 If you're looking to solve this using Appian expression language only, i.e. for on-form or other local data (not coming thru DB queries), then the steps I would do would be: 
 
 Create a local variable collecting a list of unique users 
 Use your a!forEach() loop over the list of unique users from step 1, and on each loop, find the items in the original list that match that user 
 Sum all matching item VALUEs in that iteration of a!forEach(). 
 
 This list of steps should be doable by simply exploring the functionality available within Appian especially the documentation provided at the Appian Documentation website, and building it up iteratively. If you need further help I may have the time to type up some example code a little later this evening or tomorrow morning (unless someone else beats me to it). 
 Edit: please see the below code: a!localVariables(
 
 local!data: {
 a!map(
 id: 1,
 currency: "USD",
 createdBy: "user1@gmail.com",
 value: 1000
 ),
 a!map(
 id: 2,
 currency: "USD",
 createdBy: "user1@gmail.com",
 value: 2000
 ),
 a!map(
 id: 3,
 currency: "USD",
 createdBy: "user2@gmail.com",
 value: 2000
 )
 },
 
 local!uniqueUsers: rule!GCO_RULE_General_distinct(local!data.createdBy),
 
 a!forEach(
 local!uniqueUsers,
 a!localVariables(
 local!currentUserIndices: wherecontains(
 fv!item,
 local!data.createdBy
 ),
 
 a!map(
 currentUser: fv!item,
 totalValue: sum(
 index(
 local!data.value,
 local!currentUserIndices
 )
 )
 )
 )
 )
)

Foreach data grouping by value

Top Replies