Hello Alteryx Pros !
I want to parse the 'Participants' field into corresponding fields based on the no. of participants mentioned in the 'Participants' field ( which varies ).
Check the image below for the input.
The syntax for the field is:
Lastname-(which can be one word like Ronaldo or multi-word like Del Piero ),/s(whitespace) FirstName-(which can be one word like Andres or multi-word like Cristiano Jr.)/s(whitespace) [club name+type]-(which can be one word like [JUVE] or club name plus type like [JUVE Non-R&M]
Desired Output :
The 'Participants' field must be parsed out dynamically into corresponding columns based on the no. of participants mentioned in the 'Participants'. Meaning if there are 2 participants (like record 3) it should be parsed into 2 columns 'Participant 1' and 'Participant 2' respectively and a null in the 'Participant 3'.
Imp: In the above input the max participants are 3 but there can be a case where there are 5 so the reg-ex should take a dynamic scenario into consideration and parse the field respectively.
Desired Output Image
Please suggest an optimal solution using reg-ex or any other tool for this use case. I greatly appreciate any insights/advice.
Here's the solution I came up with:
- First I changed ,\s after ] (that means, the separator of Player Names) for "|". This is just to not mix up with the ,\s from original player names.
- Then add a Record ID to keep track of the rows
- Text to Columns using the "|" separator created.
- Tile Tool with Unique Value of Record ID to create sequences of Participants
- Formula Tool to create column names (Participant + Tile Number)
- Cross-Tab tool to set up the table.
See if it works well for you.
WF attached.
Cheers.
Hi @SiddhantSurve !
Hope this finds you well. I took a similar approach to @Thableaus , just used different tools.
Added a RowID tool to mark each row as unique.
Tokenized into rows with Regex Parse on the comma+space, or end of line.
Used a Multi-Row formula tool to add the field for a column header
And finally cross-tabbed back into a table, using the new column header field as a column header.
Both approaches should work beautifully, and both are dynamic (when there are even more than 3 participants, these workflows will pick them up)
Workflow attached. Try it out and let me know what you think.
Cheers!
Esther
@estherb47 excellent advice! i had no idea i can use tokenize to rows, was looking all over for a way to dynamically force a text to columns on a string of dates separated by commas of x,x1,x2,xN input. This was great suggestion and worked for my use case!
@Mond happy to help out! Cheers, Esther