Multiple Split Columns

pauljames
pauljames Contributor
in Magic ETL

Hey, so i need to split numerous times within a column of data. i'm on the split column tile now, but i don't want to enter 100 new columns to create for each split. is there an easier way to split all of the data in the 'tags' column?

image.png
image.png

IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

Tagged:

Best Answers

  • pauljames
    pauljames Contributor
    Answer ✓

    @MichelleH Thanks Michelle! Yep, I've thought about that for sure, long-term the plan is a connector that would be pulling this into domo. I was hoping there is an magic etl automation solution for this so we don't have to manually update.…

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    I had to do this recently where I had a column with 50+ splits. One thing I did to speed things up is shown in the below recording:

    https://www.loom.com/share/671eda360b2a4d4da2741363419200c9?sid=7309d94b-1d8e-4448-90d3-1d6c7d98c205

    It's not perfect, but it does speed things up. From there I utilize the pivot/unpivot tiles to make the data more useable. Additionally with the pivot/unpivot tiles it doesn't matter how many column splits you have because they get transformed into rows.

    If I solved your problem, please select "yes" above

  • timehat
    timehat Domo Employee
    Answer ✓

    An option that may become available in the future would be to transform a list like this to JSON and use JSON functionality to expand that list as you see fit.

    For a comma-separated list like this, you'd have to probably do the following:
    1) Replace any double quotes with a backslash-double-quote

    2) Replace commas with double-quote-comma-double-quote

    3) Prefix the whole column with a left square bracket. Suffix with a right square bracket

    4) Consume the column as JSON and split the JSON array you've just constructed into columns or rows.

    I know this doesn't answer your question now, but wanted to share that this is future functionality in MagicETL to be looking forward to.

Answers

  • MichelleH
    MichelleH Coach

    @pauljames I noticed that you're using a spreadsheet upload as your input dataset. Depending on how often you need to upload the file, you could try using Excel's text to columns function to break out the columns before uploading to Domo.

  • pauljames
    pauljames Contributor
    Answer ✓

    @MichelleH Thanks Michelle! Yep, I've thought about that for sure, long-term the plan is a connector that would be pulling this into domo. I was hoping there is an magic etl automation solution for this so we don't have to manually update.…

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    I had to do this recently where I had a column with 50+ splits. One thing I did to speed things up is shown in the below recording:

    https://www.loom.com/share/671eda360b2a4d4da2741363419200c9?sid=7309d94b-1d8e-4448-90d3-1d6c7d98c205

    It's not perfect, but it does speed things up. From there I utilize the pivot/unpivot tiles to make the data more useable. Additionally with the pivot/unpivot tiles it doesn't matter how many column splits you have because they get transformed into rows.

    If I solved your problem, please select "yes" above

  • timehat
    timehat Domo Employee
    Answer ✓

    An option that may become available in the future would be to transform a list like this to JSON and use JSON functionality to expand that list as you see fit.

    For a comma-separated list like this, you'd have to probably do the following:
    1) Replace any double quotes with a backslash-double-quote

    2) Replace commas with double-quote-comma-double-quote

    3) Prefix the whole column with a left square bracket. Suffix with a right square bracket

    4) Consume the column as JSON and split the JSON array you've just constructed into columns or rows.

    I know this doesn't answer your question now, but wanted to share that this is future functionality in MagicETL to be looking forward to.

Categories