Reg-ex Dynamic Parsing

[SiddhantSurve]

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. 

[Thableaus]

Hi @SiddhantSurve 

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.

[estherb47]

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

[Mond]

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

[estherb47]

@Mond happy to help out! Cheers, Esther