This I read in, I assigned a random header to it (don’t need it, but something to identify them by).
The codes I highlighted get associated with another file and get assigned locations based on the codes. Result looks like this:
Now, the issue. I need to consolidate the values in the columns that have the same location on the first row. (MEX, MEX, USA…)
Best example would be Row 17.
The last (visible) column is the total, but it’s for all locations.
I want to have a column with the sum of all MEX values, another one for all NIC values and so on.
Example of finished output (done in excel with SUMIF):
To Row total is not needed, I have it in the original export, all I need is the sum per location.
I have no limitations on column names or anything, so any solution goes.
Thank you.
Never mind, figured it out on my own, adding solution for other struggling Knimers
I used a Transpose node to shift my row of interest into a column, eliminated the rows that were of no interest and used a GroupBy Node to do the SUM.
Traspose → moves the locations from row to column
Row Filter → Removes rows NOT part of the SUM, but just first 2 as in my use case, my first 2 rows are constant, but I don’t know how many locations I can have
Missing Values → removes everything “after” the locations, thus containing only the locations
String to number → self explanatory
Group By → Row 3 contains my locations, and then I just used the Pattern Based Aggregation so I don’t have to SUM the many rows manually:
