Transform Data

TMonty0319 Member

October 1 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

0

Best Answer

• 

  MarkSnodgrass Coach

  October 1 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 any users posts that helped you.
  **Please mark as accepted the ones who solved your issue.
  

  1

Answers

• 

  nmizzell Contributor

  October 1

  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.

  0
• 

  MarkSnodgrass Coach

  October 1 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 any users posts that helped you.
  **Please mark as accepted the ones who solved your issue.
  

  1
• 

  TMonty0319 Member

  October 1

  Thanks Mark this worked for my dataset!

  0