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.

pstrauss

So I'm facing a similar issue where I have a bunch of multiselect values where multiple values are stored in a single comma-separated column. I have some logic in my ETL that does a pivot/unpivot in order to be able to report on them, but it's caused a MASSIVE row explosion with about 10 columns I have to do this with.

Can you guys think of a way to do something with a beast mode function on the report itself to split on commas and count multiple values from a single column instead?

Basically, my data looks like this

ID column 1         column 2

1   apple,cherry,banana    blue,green,red

2   cherry,apple       red,blue

3   NULL           green,blue

And I want to be able to generate two cards that looks like this

Count of Column 1

apple 1

cherry 2

banana 1

Count of Column 2

blue 3

red 2

green 2

ST_-Superman-_

If you know the possible values for each multi-select answer, you could create different calculated fields for each. Something like this:

`apple`

sum(case when `column 1` like '%apple%' then 1 else 0 end)

You would then do similar fields for cherry, banana, blue, red, green...

Then you can create a table card and drag these calculations into the columns section:

You can also use the "Transpose" option in the general chart properties to swap the columns and rows: