Break out rows depending on columns having a value

jrtomici · 2024-05-09T18:14:45+00:00

There was an error rendering this rich post.

Hi, I have a dataset that looks like this:

Name      ID1      ID2      ID3      ID4
A         1        2        3        4
B         5        6        7
C         8        9
D         10

I want to break out these Names by the different IDs, like so:

Name      ID
A         1
A         2
A         3
A         4
B         5
B         6
B         7
C         8
C         9
D         10

How would I approach this in Magic ETL? Thank you.

Find more posts tagged with

Transformations

Magic ETL

Data Flows

Accepted answers

ColemenWilson

You would use the pivot/unpivot tile in Magic ETL: https://domo-support.domo.com/s/article/360044951294?language=en_US There are examples in the KB article that walk through exactly what you are trying to do. Let me know if you get stuck!

All comments

ColemenWilson

jrtomici

This is exactly what I needed, I always forget about this tile. Thank you!

david_cunningham

@jrtomici the answer provided by @ColemenWilson is the correct one, and I kindly ask that you please select his answer as the best response. I just wanted to provide you with a quick breakdown of how to achieve it.

Example Data

You'll use the dynamic unpivot tile, selecting "Name" to not be pivoted and then assigning whatever name you want to give your column name and column value columns. In this case I choose ID and Value

Then you can filter to where Value is not null to remove rows where there wasn't a value for that ID

Lastly, you can select the columns you desire in your output. In this case, I matched the output you showed in your post.