We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

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

Parsing columns to create new columns

SouravKayal
8 - Asteroid

Hi Team,

 

I have a column of data in a text like

 

Column name:

abcde;def;567;name1:bk;name2:bh

 

I would want to take out values name 1 as a column with value as bk and name 2 as a column with value as bh 

 

the column has a lot of bad data as well.

 

what is the best way to achieve this

13 REPLIES 13
FinnCharlton
12 - Quasar

Hi @SouravKayal , you can do this with the Parse tools and the CrossTab tool:

FinnCharlton_0-1674660086164.png

Hope this helps

 

JamesCharnley
12 - Quasar

@SouravKayal  you're essentially looking to get the data in the correct form to cross-tab. You may need to add another step to this in your data, making a tag to group on in the crosstab to stop concatenation.

 

JamesCharnley_0-1674660228613.png

 

Edit: Also realised I missed that it looks like you might be talking about a column name. If so then Finn's solution below is perfect

FinnCharlton
12 - Quasar

@SouravKayal , have realised this is a column name? You can put this into a field with the multi-field tool, and then do the same:

FinnCharlton_0-1674660265110.png

 

SouravKayal
8 - Asteroid

dint work for me ,  may be i gave the incorrect format so the input data is like

 

number: 123; Rate: 3; Status:XYZ; Type: FULL; Owner: TOM; Owner2: BOB

Christina_H
12 - Quasar

How about this?

Christina_H_0-1674661193725.png

binuacs
17 - Castor

@SouravKayal One way of doing this

 

binuacs_0-1674661253006.png

 

JamesCharnley
12 - Quasar

@SouravKayal  Finn's answer isn't catching it because there's a space in your real data after the : which would need to be reflected in the RegEx. Mine is looking for the word name instead of Owner. Both should be fairly easy fixes depending on what you're looking for.

FinnCharlton
12 - Quasar

@SouravKayal :

Seems like you don't even need the regex tool if you want to keep all of the name:value pairs. Try this:

FinnCharlton_0-1674661282781.png

 

SouravKayal
8 - Asteroid

Thank you i could make some changes and make this work now if i have multiple columns to parse in the same data set should i just create more of these flows

Labels