Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Splitting comma separated values in a field into multiple rows

ccostello
7 - Meteor

Hi all!

 

Hoping someone can help.

 

Please see below sample data:

 

DrinkCard or CashPrice
Coffee, Tea, CokeCard80p
CoffeeCash80p
CoffeeCard80p
Tea, CokeCard80p

 

I want to separate out the comma separated values into separate rows but keep the data in all other columns. I would want the above to look like the below:

 

DrinkCard or CashPrice
CoffeeCard80p
TeaCard80p
CokeCard80p
CoffeeCash80p
CoffeeCard80p
TeaCard80p
CokeCard80p

 

What is the best way to do this please?

 

Please note also within the same data set have the same issue where some values are separated by '/'

 

Thanks so much!

5 REPLIES 5
Blake
12 - Quasar

Hi @ccostello 

 

Use the text to columns tool but select "split to rows"

 

Blake_0-1581693695997.png

 

Although my screenshot doesn't show it, you can toss both delimiters in the tool. 

 

See the attached example and please mark this as solved if it accomplishes what you are trying to do.

ccostello
7 - Meteor

Thanks so much that was so quick and easy! Fixed my issue- appreciate it

cff026
5 - Atom

Hey 

Is it possible to do this but also to split the value so as not to duplicate the cost - e.g. in the example provided above (copied again below):

 

Going from: 

 

DrinkCard or CashPrice
Coffee, Tea, CokeCard80p
CoffeeCash80p
CoffeeCard80p
Tea, CokeCard80p

 

Separate out the comma separated values into separate rows but keep the data in all other columns except for price which I would want to divide by the number of items I'm splitting. I would want the above to look like the below:

 

DrinkCard or CashPrice
CoffeeCard26.6p
TeaCard26.6p
CokeCard26.6p
CoffeeCash80p
CoffeeCard80p
TeaCard40p
CokeCard40p
atcodedog05
22 - Nova
22 - Nova

Hi @cff026 

 

you might need additional processing for this.

atcodedog05
22 - Nova
22 - Nova

Hi @cff026 

 

Here is a workflow for the task. By taking the count and dividing the price

Output:

atcodedog05_0-1606064628325.png

Workflow:

atcodedog05_1-1606064664635.png

Hope this helps 🙂


Give a like if you dont mind 😀👍

Labels