Flatten a table where a field can have multiple values

#1

i am doing a web request via api which returns a table similar to this:

Screen Shot 2021-06-17 at 11.18.43 AM
Screen Shot 2021-06-17 at 11.18.43 AM1050×360 20.6 KB

Was trying pivots but i cannot get this right.

Thanks,

Cris

#2

A few things going on here. Metrics need to be aggregated, and tags need to be pivoted. So it would make sense to process them separately. See the example below.

aggregate-pivot.morph (4.6 KB)
Book1.xlsx (8.7 KB)

image
image1430×475 27 KB

Gotchas:

  • The “Merge” must be in the left join mode
  • “Running total” must be grouped by account ID
#3

Thanks Dmitry. That worked great!!!

#4

Luckily my metrics that i get via api are pre-aggregated so when separate tags, flatten, unpivot/pivot it all (my base and the tags only tables) so i have one record in my tags with all the individual tag1, tag2, etc. then merge back to my original table by the unique client id everything is good. The separation of the tags was the key. Appreciate your help!

#5

You’re welcome :slight_smile: