Splitting up multiple-value fields into multiple rows

Hello,

I have a dataset like this:

ID    Num
1     10
2     20, 30
3     40, 50, 60

I want to use Magic ETL to break these out so each row has one "Num":

ID   Num
1    10
2    20
2    30
3    40
3    50
3    60

Thank you

Tagged:

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    There isn't a way to programmatically do this but you can utilize the SPLIT_PART function in a formula tile to pull the specific one you want. You'd need to have multiple formula tiles to pull the different number out then stack everything back together with an append rows tile. You'd need to do this for as many different values you may have in your list.

    To get the first value in the list:

    split_part(`Num`,', ',1)
    
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @GrantSmith thank you. Unfortunately the dataset is far too large and has far too many different numbers listed in that column to use the split function.

  • GrantSmith
    GrantSmith Indiana 🥷

    You could attempt to utilize a MySQL dataflow which would give you a bit more flexibility in this case but will execute slower. Here's an example I found which you could use as a template: https://stackoverflow.com/questions/5041537/mysql-csv-row-to-multiple-rows

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @jrtomici Do you have a maximum number of items that could be in your comma-separated list? If so, you could use the Split Columns tile in MagicETL to break them each out. Then you would need to create a branch of the dataflow for each split to filter to the rows that have a value in that position and rename the field you filtered to "Num". After that you can use an Append Rows tile to merge all your splits back together. Unfortunately it's a somewhat tedious setup, but that's how I've been able to work with it.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    @jrtomici I did something similar where I break up words into individual values and then put them on each row. I walk through how to do it in this video. Hopefully this will help you.


    **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.
  • Thank you everyone for your responses, especially @MarkSnodgrass that is a very clever implementation that I may find use for in the future. I don't think these solutions can apply but thank you.