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!
@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!
Hi @madhurinani
Hope you checked my solution
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
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.
@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?
Can you please share the sample input.
It seems you have executed with total different input.
@ShankerV : Here you go.
Thanks for all the help on that! Much appreciated.
@martinding : I have tried using my actual data. I think its not working as intended. Can you help me fix that?
attaching the file
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?
@martinding : it will remain the same.
no new additional columns would be populated.
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.