Splitting up multiple-value fields into multiple rows
Answers
-
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!**0 -
@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.
0 -
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!**1 -
@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.
2 -
@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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
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.
0 -
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
0 -
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:
2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive