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.

Output

Parse

Dynamic Processing

Preparation

Accepted answers

All comments

Robin_McIntosh

You'll want to use Text to Rows.

Discussion_Text_to_Rows.yxmd

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%20Expressions

Ira Watt
Technical Consultant
Watt@Bulien.com

Workflow.yxmd

Kinga

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

Workflow4AmrithKandlur.yxmd

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.

Workflow4AmrithKandlur.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335