Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Splitting comma separated values in a field into multiple rows

Highlighted
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!

Highlighted
Alteryx Certified Partner

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.

Highlighted
7 - Meteor

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

Highlighted
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
Highlighted
17 - Castor

Hi @cff026 

 

you might need additional processing for this.

Highlighted
17 - Castor

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