How to Trim the string data and paste in different columns based on a specific rule?

[madhurinani]

Hello All,

I have the data in below format:

Column 1	Column 2
test 1	Name: abc, brand: kia, type: EV, color: blue
test 2	Name: xyz, brand: bmw, type: EV, color: white
test 3	Name: qwe, brand:audi, type:gas, color: red

I would like the output as follows:

Column 1	Name	brand	type	color
test 1	abc	kia	EV	blue

Some of the column headers might not have the colon(:) and might just be "Name abc". For this I think I might need to create a specific rule for that.

Is that something that can be achieved in Alteryx?

Any help would be appreciated.

Thanks! 

[madhurinani]

@binuacs : This is only working if the words are single words

If I try to add a two-worded brand name or anything. it breaks unfortunately

Thanks!

[ShankerV]

 Hi @madhurinani 

Hope you checked my solution 

@madhurinani 

Please find the dynamic way to reach the solution.

Input with and without colon

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[martinding]

Hi @madhurinani

Another approach is to use the Regex tool.

Please find below a one tool solution. In the workflow, I've also tested a number of special cases, including no colon (:) and 2 word brand names.

 

 

[madhurinani]

@ShankerV : I believe there is one more small issue.  I was using my actual data.

If you notice below, since the column header names are the same in the column to split option, it gets concatenated in the next step as shown. Can you maybe have the column name as Account Name, Account Number as a whole?

[ShankerV]

@madhurinani 

Can you please share the sample input.

It seems you have executed with total different input.

[madhurinani]

@ShankerV : Here you go.

 Thanks for all the help on that! Much appreciated.

[madhurinani]

@martinding : I have tried using my actual data. I think its not working as intended. Can you help me fix that?

attaching the file

[martinding]

Hi @madhurinani ,

Will the max number of columns and column names stay the same?

Or could there be additional unknown columns that appear after FFC in the future?

[madhurinani]

@martinding : it will remain the same. 

no new additional columns would be populated.

[martinding]

Hi @madhurinani ,

I've used a 2-regex tool structure here.

The first regex tool is used to parse information common to all users (e.g. mandatory information that all users have), here I have used information up to Account number, but it doesn't have to be Account number, you can adjust it to suit your actual data.

The 2nd regex tool is used to parse additional/optional information. 

To be honest, these can be all written inside a single Regex Tool, but for clarity (personal preference), I like to keep mandatory information and optional information separate when parsing.