cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wiredupjax
Resolver I
Resolver I

Stumped: Accumulate sales for variable number of individual in unordered table

Dear community,

 

I'm stumped - here's the situation:

  • I read in a CSV file, parse it into a JSON array - all good
  • I can accumulate sales based on a location because the locations are ordered (i.e., one location, x rows... next location, n-rows)

Now, I want to accumulate sales based on a variable number of PEOPLE which is unordered

 

I've tried:

  • read in file via Parse JSON body, apply-to-each and write into an array--but there's no way to sort an array

 

I'm stumped--so, how do I accumulate sales that occurred on a date and has an unsorted row of n-number of people

 

e.g.,

Jason, 20

Mike, 17

Jason, 2

David, 11

Jason, 7

David, 9

 

How do I accumulate by person if I don't know the names, number of names or number of records?

Best, Jason

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @wiredupjax :

Do you want to summarize "number" based on "Name"?

If so please try this solution:

1\My excel tabel

 

v-bofeng-msft_0-1606457164611.png

 

2\My flow

Entirety

v-bofeng-msft_1-1606457164614.jpeg

 

Detail1

v-bofeng-msft_2-1606457164619.png

 

Reference Expression

1\ 

 

outputs('List_rows_present_in_a_table')?['body/value']

 

2\

 

item()?['Name']

 

Note:Get the list of names through the select action

3\ 

 

union(body('Select'),body('Select'))

 

Note:Delete duplicates in name list

4\

 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Name": {
                "type": "string"
            }
        },
        "required": [
            "Name"
        ]
    }
}

 

Note:Analyze the  list through JSON action

Two variables

v-bofeng-msft_3-1606457164620.png

Note:Add two variables

Detail2

v-bofeng-msft_4-1606457164626.jpeg

Note:

Traverse the data source while traversing the name list (nested), and summarize the numbers of all records with the same name.

Reference Expression

5\ 

 

int(items('Apply_to_each_2')?['number'])

Note:Because number is a string, use int to convert it to a number for calculation

 

Detail 3

v-bofeng-msft_5-1606457164629.png

 

Note:The result is saved to the variable "TheResult"

The Result

v-bofeng-msft_6-1606457164632.png

 

Best Regards,

Bof

View solution in original post

4 REPLIES 4
v-bofeng-msft
Community Support
Community Support

Hi @wiredupjax :

Do you want to summarize "number" based on "Name"?

If so please try this solution:

1\My excel tabel

 

v-bofeng-msft_0-1606457164611.png

 

2\My flow

Entirety

v-bofeng-msft_1-1606457164614.jpeg

 

Detail1

v-bofeng-msft_2-1606457164619.png

 

Reference Expression

1\ 

 

outputs('List_rows_present_in_a_table')?['body/value']

 

2\

 

item()?['Name']

 

Note:Get the list of names through the select action

3\ 

 

union(body('Select'),body('Select'))

 

Note:Delete duplicates in name list

4\

 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Name": {
                "type": "string"
            }
        },
        "required": [
            "Name"
        ]
    }
}

 

Note:Analyze the  list through JSON action

Two variables

v-bofeng-msft_3-1606457164620.png

Note:Add two variables

Detail2

v-bofeng-msft_4-1606457164626.jpeg

Note:

Traverse the data source while traversing the name list (nested), and summarize the numbers of all records with the same name.

Reference Expression

5\ 

 

int(items('Apply_to_each_2')?['number'])

Note:Because number is a string, use int to convert it to a number for calculation

 

Detail 3

v-bofeng-msft_5-1606457164629.png

 

Note:The result is saved to the variable "TheResult"

The Result

v-bofeng-msft_6-1606457164632.png

 

Best Regards,

Bof

View solution in original post

@v-bofeng-msft  - thank you for the example.

Can you explain the logic behind the flow?

 

As in,

1st  you want to x

2nd you'll want to y

3rd ... n

 

This way rather than simply copying your solution, I can learn why. 

 

Best, JS

I don't understand what you are trying to do. Can you at least provide the CSV schema?  And why are you talking about dates when there are no dates in the list example you provided? Also, you keep writing 'sorted'. Fine. But sorted by what? Sorted by date, location, name, or total amount of sales?  

 

If you know SQL, could you state what you want as a SQL statement? That way it'll be a lot clearer what you're after. 

Hi @wiredupjax :

I have added some notes to the original reply, hope they are useful to you.

Best Regards,

Bof

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors
Users online (1,499)