We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Need to add , delimiter in Ingredients column data

jassik
8 - Asteroid

Hello Alteryx community , I  have ingredients column where I have different ingredients with different length of characters. How can I split them with comma(,) delimiter.

For eg: my data is--    WATER CORN SYRUP SUGAR MANGO PUREE MANGOES LEMON JUICE 

and I  want my output :   WATER, CORN SYRUP, SUGAR, MANGO PUREE, MANGOES, LEMON JUICE

Can you please help me with it

11 REPLIES 11
binuacs
20 - Arcturus

@jassik is your data in one row or different rows?nI mean 

WATER

CORN SYRUP

SUGAR

MANGO etc

or all in one line of data

Thableaus
17 - Castor
17 - Castor

Hi @jassik 

 

You have to first identify (or flag) the ingredients with two words or more (corn syrup, mango puree, etc.).

This will make your life easier to create the appropriate rule to replace the spaces by commas.


Thanks, 

jassik
8 - Asteroid

It is in one row and I just need to add comma to split the ingredient's name 

jassik
8 - Asteroid

Thanks for replying @Thableaus but I am not sure about this I tried different ways but i couldn't solve it

jassik
8 - Asteroid

It is in one row and I just need to add comma to split the ingredient's name @binuacs exactly how I type in

JamesCharnley
13 - Pulsar

@jassik there's no magic answer here unfortunately. There's no way for Alteryx to just know the logic behind differentiating Corn from Syrup from Corn Syrup, or every combination of adjacent words that it might come across to replace the spaces with commas. Identifying those possible two word ingredients as mentioned above is necessary, but feels likely to be a manual process if you don't have some kind of master list somewhere.

binuacs
20 - Arcturus

@jassik is there any pattern in your original data that indicates these words should be within the comma delimiter like CORN SYTUP, MANGO PUREE etc?

danilang
19 - Altair
19 - Altair

Hi @jassik 

 

As everyone here has mentioned, you need to have a list of multi word ingredients, Corn Syrup, etc.  Once you have that list, you can use something like the following 

 

 

w.png

Use a Find Replace tool to replace each of the multi-word ingredients with something not including a space, resulting in "WATER CORN CORN_SYRUP SUGAR MANGO_PUREE MANGOES LEMON_JUICE"   Notice that I added an extra CORN to your original list. After that, use a nested replace to first replace space with comma space,", " and then replace the underscores with spaces.

 

Dan

 

jassik
8 - Asteroid

yes @binuacs 

Labels