cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lopezreyjr
Regular Visitor

Get distinct values from an array, then get the total value for each distinct item

Good day everyone,

 

I have an array that holds couple of values and I need a step to get all the distinct item then get their total time (Duration) from the original array.

 

Array input:

[
  {
   "Name""Person A",
   "EmpNumber": "111111",
   "Time" : "0005"
  },
 {
   "Name""Person B",
   "EmpNumber": "222222",
   "Time" : "0010"
  },
 {
   "Name""Person A",
   "EmpNumber": "111111",
   "Time" : "0015"
  },
  {
   "Name""Person B",
   "EmpNumber": "222222",
   "Time" : "0008"
  }
]
 
Desired array output:
[
  {
   "Name""Person A",
   "EmpNumber": "111111",
   "Time" : "0020"
  },
 {
   "Name""Person B",
   "EmpNumber": "222222",
   "Time" : "0018"
  }
]
 
The desired output array will be place in html table.
 
Hope anyone can help.
 
Thank you!
Rey
2 REPLIES 2
DamoBird365
Super User
Super User

Hi @lopezreyjr 

 

Not as easy as it sounds as Flow doesn't allow you to sum more than two numbers at a time.  But I did write an article on summing here https://www.damobird365.com/want-to-sum-up-a-list-of-numbers/ which might help explain one part of this question.

 

I've stuck your array into a compose and then used a select action to get a list of employee numbers, I will then use a union on the result of this in the next stage to get a unique list of numbers (i.e. remove the duplicates, 4 down to 2).

DamoBird365_0-1625225556822.png

Next part is initialising an array for my results.

DamoBird365_1-1625225598572.png

Then an apply to each unique employee ID.  Filter the original array for a match, use select to get an array of the times and then use my method for adding an array of numbers in the Select Length action.

DamoBird365_2-1625225741833.png

I then calculate the length in a compose and append an object to the array using append to array.  I've included a compose at the end to output the new array.

DamoBird365_4-1625225893235.png

The output of which is :

DamoBird365_5-1625225950810.png

The only complaint you might have is that the time is not padded with 0's.  So....

concat(
substring(
'0000',
1,
    sub(
    4,
    length(string(outputs('Length')))
    )
)
,outputs('Length'))

 

DamoBird365_6-1625226668034.png

 

Please give that a go.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Paulie78
Super User
Super User

In addition to @DamoBird365 brilliant solution. Here is another possible way, which achieves the same result, in a slightly different way. It's always interesting to see different solutions to the same problem:

https://ibb.co/27XrLTZ

AggregateArray.png

I have copied the whole thing into a scope for you so you can easily copy it into your flow to see how it works. It requires no variables and should execute very fast. To add it your flow:

  • Copy the code below into your clipboard
  • Choose Add an action.
  • Go to my clipboard.
  • Press CTRL-V
  • Select the new scope action that appears.
{"id":"e607692e-65e8-4977-804e-7339-2672065d","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope","operationDefinition":{"type":"Scope","actions":{"Array":{"type":"Compose","inputs":[{"Name":"Person A","EmpNumber":"111111","Time":"0005"},{"Name":"Person B","EmpNumber":"222222","Time":"0010"},{"Name":"Person A","EmpNumber":"111111","Time":"0015"},{"Name":"Person B","EmpNumber":"222222","Time":"0008"}],"runAfter":{}},"Select":{"type":"Select","inputs":{"from":"@outputs('Array')","select":"@removeProperty(item(), 'Time')"},"runAfter":{"Array":["Succeeded"]},"description":"removeProperty(item(), 'Time')"},"Unique_People":{"type":"Compose","inputs":"@union(body('Select'),body('Select'))","runAfter":{"Select":["Succeeded"]},"description":"union(body('Select'),body('Select'))"},"Apply_to_each":{"type":"Foreach","foreach":"@outputs('Unique_People')","actions":{"Filter_array":{"type":"Query","inputs":{"from":"@outputs('Array')","where":"@equals(item()['EmpNumber'], items('Apply_to_each')['EmpNumber'])"},"runAfter":{},"description":"items('Apply_to_each')['EmpNumber']"},"TotalTime":{"type":"Compose","inputs":"@addProperty(items('Apply_to_each'), 'Time', xpath(xml(json(concat('{ \"root\": {\"People\": ', body('Filter_array'), '}}'))), 'sum(*//Time/text())'))","runAfter":{"Filter_array":["Succeeded"]},"description":"addProperty(items('Apply_to_each'), 'Time', xpath(xml(json(concat('{ \"root\": {\"People\": ', body('Filter_array'), '}}'))), 'sum(*//Time/text())'))"}},"runAfter":{"Unique_People":["Succeeded"]}},"Agrregated":{"type":"Compose","inputs":"@outputs('TotalTime')","runAfter":{"Apply_to_each":["Succeeded"]}}},"runAfter":{}}}

See how you get on. 

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,604)