Splitting up multiple-value fields into multiple rows

jrtomici

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

GrantSmith

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)

jrtomici

@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

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

MichelleH

@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

@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.

https://youtu.be/sXOTfUnOUVU

jrtomici

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.