Splitting records with one attribute data into multiple records

[AmrithKandlur]

Hi.,, below is a sample data that I have pasted.

Name,age,work location

John,28,London|new york|California 

Mark,32,Sydney|Canberra

Susie,40,Delhi,Bangalore|Chennai|Mumbai

I want to get the above data as below

Name,age,work location

John,28,London 

John,28,new york 

John,28, California 

Mark,32,Sydney

Mark,32,Canberra

Susie,40,Delhi

Susie,40,Bangalore

Susie,40,Chennai

Susie,40,Mumbai

Please do note that in the "work location" attribute, there is data separated using pipe and the number of pipes is not consistent. I mean, it could sometimes have two locations info or sometimes more.

Please help how I can get this done using Alteryx.

[Robin_McIntosh]

You'll want to use Text to Rows.

[IraWatt]

Hey @AmrithKandlur,

Here is one way to do this:

The text to column tool can split to rows to achieve this. The community has some quick and easy videos on the text to column tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20... 

Ira Watt
Technical Consultant
Watt@Bulien.com 

[Kinga]

I used two Text to column: first split to columns second to rows:

[AmrithKandlur]

Thanks everyone, it was of great help :)

[AmrithKandlur]

Hi All,

In addition to that, I have a challenge with the following scenario.

Name,age,Range

John,28,TJY00783 HULB002-TJY00783 HULB004

Mark,32,Sydney,TJY00456 HULB002-TJY00456 HULB006

I want the data to be like below (i am using only the first records above as an example of split below)

Name,age,Range

John,28,TJY00783 HULB002

John,28,TJY00783 HULB003

John,28,TJY00783 HULB004

Mark,32,Sydney,TJY00456 HULB002

Mark,32,Sydney,TJY00456 HULB003

Mark,32,Sydney,TJY00456 HULB004

Mark,32,Sydney,TJY00456 HULB005

Mark,32,Sydney,TJY00456 HULB006

Alteryx should recognize that there is a - in the range text, then consider using the last three characters of the text before the - and last three characters after the - and split the records as above.

[flying008]

Hi, @AmrithKandlur 

Maybe you can look the other post about this of you.

[Kinga]

hi,

I made small workflow.