I have a column with a variable number of items, delimited by a comma.
For example: column_a: 1,2,3,4
Number of elements is variable - it can be a single element, or 50 (while I can set an upper limit, it can be dozens).
I need a way to split the data in the column into rows, so with the above example I'd like to have:
Input: column_a: 1,2,3,4
With Magic ETL I'm not able to do that, nor with SQL (again, without creating a very large and ugly SQL query to span maximum 50 elements).
Is there any other way other than scripting it externally to Domo? I have this requirement for several datasets and it starts to become an issue.
Using MySQL dataflows you should be able to create a stored procedure to help you loop through the values and create more rows.
This stack overflow post would be a good starting point:
I'm trying to do the same thing and found this: https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Splitting_a_String_into_Multiple_Rows
I have tried with my data and have run both sets of SQL code and clicked apply as per the instructions. What I cannot find though is the output table 'final' nor the ability to save the data flow for future use. Can anyone advise? Big thanks in advance.
You'd want to do a statement like this in the output:
select * from final
Once there's on output you can save the dataflow.
The knowledge base article leaves it open ended as what to do next:
"You can now use the “final” table in subsequent transforms, including Output DataSets transforms."
Hi anyone out there. I've noticed that i'm missing out on contact data because the column is being truncated. I think it's something to do with the varchar size.. can anyone please guide me as to how to increase this and for DOMO to understand? I've tried putting larger numbers in but it doesn't seem to help.
Any assistance much appreciated.
Thankyou, it works perfectly now!
hello, thanks all for the hints and sharing!
however, I have no idea why the 2nd SQl(CALL string_split_procedure) does not work on my case (attached the screenshots for reference) - that shows:
Whoops! Something went wrong.
after running the SQL for few minutes
appreciate for any advice and solution to the above, thanks a lot!
@Nek I wonder if you found a solution for this
Been trying to use the dataflow.
I was also getting the same error for the 2nd SQl call function (CALL string_split_procedure)
"(Whoops! Something went wrong.)"
Let the thread know how to resolve this if you have the solution.
hey friends ... wow super old thread! But this trick is a good one.
Here's a solution in Redshift that may work a little faster because the MySQL proc does not use indexing and these string operations get expensive.
That said for TROUBLESHOOTING MySQL Stored Procedures...
Keep in mind this is just MySQL. So figure out what's broken.
1) are you executing the Call properly?
If yes, the stored proc takes a while to run. So instead of trying to SELECT* FROM FINAL try SELECT * from one of the intermediate tables.
If you dissect your stored proc, you'll see that it has multiple CREATE table arguments but no test to see if it already exists. This means you can only call your stored proc ONCE. If you try calling it again, it will fail because your proc will try to CREATE a table that already exists and then error out.
SIDE NOTE. To test if you're calling the stored proc correctly, take all the guts out of the CREATE PROCEDURE statement. Then try calling it. If it works, then reload the page, throw all the guts back in and try again. If it fails, slowly pare back the code until you figure out the line that breaks.