Multiple Split Columns

pauljames

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?

pauljames

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

ColemenWilson

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.

timehat

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.

MichelleH

@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

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

ColemenWilson

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.

timehat

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.