Split Multi Value Column values into Separate Columns

gwenpowers

Hi! I have a column with multiple values, separated by a comma. I am able to use the Split Column in Magic ETL and place the values into new columns. However, I need it to be added into a new column based on the value.

For ex.:

Fruit, Vegetables (Fruit would go in Fruit Column and Vegetables would go in Vegetable Column)

Dairy, Vegetables, Meats (Meats would go in Meat Column, Dairy would go in Dairy Column, and Vegetables would go in Vegetable Column)

Currently, the new columns just add the 1st and 2nd values - So the new column would have both Fruit and Dairy in it.

MarkSnodgrass

You would need to do this in a two step process. In your split columns tile, don't try and label the columns as fruit, vegetable, etc, yet because you don't know what is what yet. Instead, label the split columns as split1, split2, split3, etc.. for however many splits you are doing.

After the split columns tile, add a formula tile. Here is where you will create your fruit, vegetable, etc. columns. In the formula tile, create a column called Fruit and use a formula like this:

CASE WHEN split1 LIKE '%Fruit%' THEN 'Fruit'

WHEN split2 LIKE '%Fruit%' THEN 'Fruit'

WHEN split3 LIKE '%Fruit%' THEN 'Fruit'

END

You would add additional WHEN statements if you have more split columns to look at. You would then use this same logic for meat, but replace Fruit with Meat. It's also important to note that it is case-sensitive, so you may want to also wrap a LOWER() function around split1, etc. and then have your like word be all lowercase.

Hope this makes sense and is what you are wanting to do.