Break out rows depending on columns having a value


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.

Best Answer


  • ColemenWilson
    Answer ✓

    You would use the pivot/unpivot tile in Magic ETL: There are examples in the KB article that walk through exactly what you are trying to do. Let me know if you get stuck!

    If I solved your problem, please select "yes" above

  • jrtomici
    jrtomici Member

    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.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**