Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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

Split rows base on value

Highlighted
Alteryx Partner

Hi Team ,

 

I'm looking for a tools to split my data set accordingly.  The original data like below
Example :

Input : A001 Day 4 and B001 Day 1,3,6

 

Output:

A001, Day 4
B001 , Day 1
B001 ,Day  3
B001, Day  6

Highlighted
11 - Bolide

You could use regex something like:

 

(\w\d+\s)Day\s(\d+)\sand\s(\w\d+\s)Day\s(\d+),(\d+),(\d+)

 

parse it to several columns

 

PaulFound_0-1573034378018.png

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @kflow I also used RegEx, but broke it into a couple of steps:

OllieClarke_0-1573037060429.png

Highlighted
17 - Castor
17 - Castor

hi @kflow 

 

Does your data really come in the form "A001 Day 4 and B001 Day 1,3,6"?  What if you get a record for C001? Does it add an "and".

 

Could it actually come in on separate rows like 

A001 Day 4 

B001 Day 1,3,6 

 

Dan

Highlighted
Alteryx Partner

hi@danilang,

 

The original data is like below:

A001 Day 4 

B001 Day 1,3,6 

Highlighted
Alteryx Partner

You can do it this way : 
From this :

Julien_B_1-1573127260116.png

to that : 

Julien_B_0-1573127221236.png

Highlighted
12 - Quasar

Hi @kflow ,

Try text to columns ( space delimiter) followed by  another text to columns ( , delimiter and split to rows ) .

benakesh_0-1573128670070.png

 

Highlighted
17 - Castor
17 - Castor

Hi @kflow 

 

Thanks for the clarification.  This workflow uses a regex formula to split the input columns into Type and Day and then a text to columns to split the day numbers into rows

w.png

After that, it's a question of how you want the output.  The top output is the most useful for further processing since [Day number] is a single numeric value that you can convert to a day name or a date.  The second output includes the "Day" label in the [Day] field.  the third output is similar to the output you included in the original post

 

Dan

Highlighted
Alteryx Partner

Thanks for your help, i'm able to do what i want with the simple flow.

Labels