Convert one column to multiple columns based on column values

[VishyReddy]

Hi, I have recently started using Alteryx. I am trying to convert one column into multiple columns based on the column value. Can some one tell me the workflow to achieve this. See below

[MarqueeCrew]

@VishyReddy,

1) parse the label data using text to columns, using to rows option. Delimiter is comma. Name is key

2) use a formula tool to update label with

replace([label],” - “,”|”)  

note:  I’m typing on my iPhone and the quote marks won’t function properly in the formula. 

3) parse the label field on the pipe character

your data will be name, label, label1, label2

4) now use a cross tab tool with name as key, label1 as header and label2 as value. 

Im fairly certain that this is what you need to solve this challenge. 

Cheers,

Mark

[Syarifhidayat]

Hi @VishyReddy,

Here I attached a workflow so that you can get a better understanding. Just as @MarqueeCrew has said, I add several more tools to get exactly like your desired output. Hope this workflow solve your problem.

Sincerely,

Syarif

[VishyReddy]

Hi Syarifhidayat,

Thanks for the solution. I am having trouble implementing on my actual data set. In addition to the the 'Labels' column, My actual dataset has 'Checklist' and 'checklist Item'. BP in Labels is same as BP Impact in Checklist. CH, CU and BP can have multiple values. I have attached the full dataset. Thank you very much

[Syarifhidayat]

Hi @VishyReddy,

I'm not understand how your output is generated. I have a few question on it:-

1) Is it all value for column Funding Source is equal to CC?
2) For Impact column, is it the value is High if BP=Retail and Medium if BP=Sales?

If what I assume is correct, this is my workflow that may help you.

[VishyReddy]

Hi Syarifhidayat,

Yes, as the name suggests, Checklist is like a question and Checklist Item is like answer(you can select more than one answer). 

Even with the labels, in the workflow you have posted, any of the CH, CU, BP or PR can have multiple values not just CH. In some case CH can be single value and BP can have 3 values. 

Thanks,

Vishi

[VishyReddy]

Attached is the sample data.

[jrgo · Accepted solution]

@VishyReddy,

Take a look at the attached packaged workflow. Note that I did NOT create a process to transform the "Checklist" and "Checklist Item" fields, primarily because I don't fully comprehend how these are to be handled, but from what I was able to gather, it sounds like something similar to what this solution provides for processing the "Labels" field. And if so, this should give you some ideas on how to finish up that last piece.

The benefit with this solution is that if the fields that need to be split up were to increase, this would automatically adjust for it without the need to add more Multi-Row formula tools to fill down the values.

Hope this helps!

Jimmy

[VishyReddy]

Hi @jrgo ,

Thanks for the workflow. It worked perfectly.

[VishyReddy]

Thanks for the workflow. It did not fully meet my needs as Funding source was hard coded. Thank you.