Unanswered

Aggregating data in a collection

(0) Share

Report

Posted on by dazftw

Hi all I have a collection called colResponses with data that looks as follows:

 {
 name: "Doe, John",
 email: "john.doe@mail.com",
 officeLocation: "Boston",
 date: "5/3/2020",
 questionnaire: 1,
 flagged: false,
 question1: "Question 1",
 question2: "Question 2",
 question3: "Question 3",
 completed: true
 },
 {
 name: "Small, Alice",
 email: "alice.small@mail.com",
 officeLocation: "Boston",
 date: "5/4/2020",
 questionnaire: 1,
 flagged: false,
 question1: "Question 1",
 question2: "Question 2",
 question3: "Question 3",
 completed: true
 },
 {
 name: "Murphy, John",
 email: "john.murphy@insight.com",
 officeLocation: "Boston",
 date: "5/5/2020",
 questionnaire: 1,
 flagged: false,
 question1: "Question 1",
 question2: "Question 2",
 question3: "Question 3",
 completed: true
 },
 {
 name: "Doe, John",
 email: "john.doe@mail.com",
 officeLocation: "Boston",
 date: "5/5/2020",
 questionnaire: 2,
 flagged: true,
 question1: "Question 1",
 question2: "Question 2",
 question3: "Question 3",
 completed: true
 },
 {
 name: "Small, Alice",
 email: "alice.small@mail.com",
 officeLocation: "Boston",
 date: "5/7/2020",
 questionnaire: 2,
 flagged: false,
 question1: "Question 1",
 question2: "Question 2",
 question3: "Question 3",
 completed: true
 },
 {
 name: "Murphy, John",
 email: "john.murphy@insight.com",
 officeLocation: "Boston",
 date: "5/7/2020",
 questionnaire: 2,
 flagged: true,
 question1: "Question 1",
 question2: "Question 2",
 question3: "Question 3",
 completed: true
 }

I am trying to achieve what I thought would be some simple aggregation but having some issues.. Ideally i'd like to have one collection grouped by distinct email and use a 'summed' value for the flagged column so it would look like this... Basically if flagged is true in either questionnaire 1 or 2 the flagged column will get set to true.

{
 name: "Doe, John",
 email: "john.doe@mail.com",
 officeLocation: "Boston",
 date: "5/3/2020",
 questionnaire: 1,
 flagged: true,
 question1: "Question 1",
 question2: "Question 2",
 question3: "Question 3",
 completed: true
 },
 {
 name: "Small, Alice",
 email: "alice.small@mail.com",
 officeLocation: "Boston",
 date: "5/4/2020",
 questionnaire: 1,
 flagged: false,
 question1: "Question 1",
 question2: "Question 2",
 question3: "Question 3",
 completed: true
 },
 {
 name: "Murphy, John",
 email: "john.murphy@insight.com",
 officeLocation: "Boston",
 date: "5/5/2020",
 questionnaire: 1,
 flagged: true,
 question1: "Question 1",
 question2: "Question 2",
 question3: "Question 3",
 completed: true
 },

I have tried a few different approaches but I think this probably makes most sense:

ClearCollect(
 colRollup,
 Distinct(
 colResponses,
 email
 ),
 AddColumns(colResponses, "flagged", /* code to sum/aggregate flagged boolean for each distinct email */ ) 
);

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

WarrenBelz 156,397 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@dazftw
The GroupBy function will provide this - one record for each email address and the rest of the data in a table you can name
GroupBy( colResponses, email, "OtherData" //call this whatever you want. )
You would have email as the primary field and (for example) flagged would be available as OtherData.flagged.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
dazftw 8 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey Warren, That does group everything but I would like to aggregate the boolean data in the "OtherData.flagged" nested collection.

It would just have a column 'flagged' that is either true or false based on the nested data.

Was this reply helpful? Yes No
dazftw 8 on at

Like
a
(0)

Report
Copy link

Link copied!
I believe I have come up with a solution but would love to know if there is a cleaner way of accomplishing this:

ClearCollect( colInitialRollup, GroupBy( colResponses, "email", "flagged" ) ); ClearCollect( colRollup, AddColumns( colInitialRollup, "Flagged", If( Sum( flagged, flagged ) = 0, false, true ) ) );

I need to do some more testing with different amounts of nested value to make sure I am getting the correct value

Was this reply helpful? Yes No
WarrenBelz 156,397 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @dazftw ,
Firstly - you might have a look at these two posts of mine a little while ago for some logic - I do not know what your Flagged column looks like, or the logic you are using here. You do not need to state If/true/false for a Boolean test, so try this
ClearCollect( colRollup, AddColumns( GroupBy( colResponses, "email", "flagged" ), "TotalFlag", Sum( flagged, flagged )=0 ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
WarrenBelz 156,397 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @dazftw ,
Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No