Transform Data

TMonty0319
TMonty0319 Member
in Magic ETL

I have data with Plants and Assets. I need to transform so i have data in each row and not multiple assets separated by commas in each row. How do I do this in Domo?

Current Structure

Plant

Asset

Shutdown Reason

Plant A

1, 2, 3, 4

Maintenance

Plant B

3,4,5

Other

Structure Need

Plant

Asset

Shutdown Reason

Plant A

1

Maintenance

Plant A

2

Maintenance

Plant A

3

Maintenance

Plant A

4

Maintenance

Plant B

3

Other

Plant B

4

Other

Plant B

5

Other

Best Answer

Answers

  • nmizzell
    nmizzell Contributor
    1. split asset column by delimiter comma. use 10 splits to be safe, since there is not an option to use as many splits as needed.
    2. unpivot the columns you created with the splits. This will turn the columns into rows, repeating the non-pivoted columns "Plant" and "Shutdown Reason" for each new row.
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Here's a video on how to split values dynamically when you don't know how many times you will have to split.

    https://youtu.be/kxjJ0T4Q4OE
    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • TMonty0319
    TMonty0319 Member

    Thanks Mark this worked for my dataset!

Categories