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

[lopezreyjr]

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

[DamoBird365]

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

Next part is initialising an array for my results.

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.

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.

The output of which is :

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'))

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]

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

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 😘