Splitting up multiple-value fields into multiple rows

jrtomici Member
edited March 2023 in Datasets


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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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.

  • @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.
  • 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
    pstrauss Member
    edited February 2023

    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

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


    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:

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman