Break out rows depending on columns having a value

jrtomici
jrtomici Member
in Magic ETL

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.

Tagged:

Best Answer

Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    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!

    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
    david_cunningham Coach

    @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

    image.png

    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

    image.png

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

    image.png

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

    image.png

    David Cunningham

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

Categories