In an array, sum values in some columns based on unique values in another column

[Gurdas]

I have a 2D array with 40 columns. One of the columns represents modes of operation. I want to find the sum of values in other columns for each of the modes (or values) in my reference column. The modes in reference column occur continuously. So, for example, if rows 5 to 7643 in the reference column have a value of "1", then that value will not occur outside of those rows. I want to find the sum of values in other columns when the reference column is "1" (and for "2", "3"...).

This is very easily done with a pivot table in Excel, just to further explain what I am looking for.

[altenbach]

---

@Gurdas wrote:

This is very easily done with a pivot table in Excel, just to further explain what I am looking for.

---

It's also very easily done in LabVIEW. 😄 You can use plain loops or even a maps based solution (LabVIEW 2019). Is the reference column the first, last, or some other random column to be determined at runtime? (A maps based solution works equally well even if the reference column is not sorted)

Typically you get better help of you make it a little bit easier for us. For example you could attach a VI containing a diagram constant with typical data.

[altenbach]

Here's a solution based on maps (LabVIEW 2019 only!).

[Gurdas]

Thanks @altenbach. Sorry it took me a while to respond. I am on LV2018 so I could not try out the maps based solution right away. I ended up writing a simple loop function to parse through each row and for now that works. Maps looks exciting, so thanks for bringing that to my attention. I will update to LV2019 later this week and see what else I can do with maps. Is there a comprehensive introduction PDF/webpage to maps?

[altenbach]

Pre-2019, you could use variant attributes, but if the datasets are not gigantic, it is not really needed.

For a non map based solution, further simplifications are possible if you know the set of unique IDs beforehand, and you probably do.

Unfortunately, the 2019 help is not online, but once installed, you can look at the 2019 help. Look in the new "collection" palette.

Also have a look at my 2019 NI_week presentation (See also this discussion)

[altenbach]

Also note that the map solution is most universal, because we don't need to know the values and number of possibilities for the key column.

If we know that the unique keys are consecutive small integer as in your case (?), things can be further simplified and maps are not really needed (bottom code).