Separate comma delimited values into rows

[4c278664ee974166e813]

Hello,

I have this data set with one column which has multiple values (possible to have only one value too) in it, separated by comma. I would like to separate them into rows.

Desired output:

Feedback Triage Team

Feedback Triage Team

Tester Team

Customers

Tester Team

Customers

etc.

UPDATE!

I tried this as first step to separate them and then possibly to unpivot to put them into rows.

But it did not work as I wanted, cause this is splitting only on the first comma, so it separates first and second value. But if there are more than 2 values in the cell then rest of the values are with the second value in a new column's one cell.

[APrasad_Tri]

Hi @Giorgi Gobronidze? , thank you for your question!

The approach to splitting into columns is correct. The number of split columns has to increase to the desired outcome and then unpivot the columns.

The recipe steps and final outcome. 

Another approach is to branch out recipes and union them into the final recipe. The number of branch recipes is totally dependent on the number of split columns created. You can also unpivot a few columns and union the other columns depending on the data size to get better performance.

In this, the number of recipes will be 3.

Recipe 1 (Split into rows): splitting the data into desired columns takes care of it. Here, we are splitting the data into 2 - Data1 and Data2 as shown below

Recipe 2 (Data1): Branch out to another recipe from Recipe 1 and delete column Data2 as shown below

Recipe 3 (Data2): Branch out another recipe from Recipe 1 again and delete column Data1 and clean up the empty rows as shown below

Go back to Recipe 2(Data1) and perform UNION with Data2. As the final step, you can sort the rows based on the primary key or ID to get the values placed as desired.

Hope this helps. Please let us know if you are having any issues.

Best,

Apeksha Prasad

[4c278664ee974166e813]

Hello APeksha,

Thank you for your reply.

What if I dont know how many split I want to make? Meaning, I dont know max amount of values that needs to be separated. It can be today 3 and tomorrow with updated date it can be 5

[APrasad_Tri]

Hi @Giorgi Gobronidze?,

The number of split columns is set manually to create a static schema for further transformation or data preparation. 

For more information, please refer to our documentation: Split Transform

Best,

Apeksha Prasad

[4c278664ee974166e813]

I found the better option. I transformed it first to array type. and then extract the list to rows.