Parsing columns to create new columns

SouravKayal

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

Best Practices

Accepted answers

All comments

FinnCharlton

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

Hope this helps

Parse and CrossTab.yxmd

JamesCharnley

@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.

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

@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:

Parse and CrossTab.yxmd

SouravKayal

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

How about this?

Parse text to column.yxmd

binu_acs

@SouravKayal One way of doing this

Parse_012523.yxmd

SouravKayal

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

JamesCharnley

@SouravKayal I think all you'll need to do is add a transpose step to the start of the workflow to turn all of the columns into rows

FinnCharlton

@SouravKayal you could deal with multiple columns using a transpose tool, rather than creating more flows. Here's an example:

Parse and CrossTab with multiple fields.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703