Using Regular Expressions to Reorganize Data

[rishipradeep10]

Hey everyone, I am an amateur Alteryx user, and I wanted to use Alteryx to create a workflow that would take parse through the information in the first three columns of the table below and organize it in two new columns (the last two "To Be" columns). It seemed like using Regex would be the best way to do this, but I have been struggling to write the correct Regular Expression because some cells have multiple comments I have to parse information from while others may only have one comment. I wanted to reach out to this community to see if anyone else had any ideas for how to do this. Thank you in advance!

Header Comment Created By	Header Level Comments	Header Comment Date	To be: (Comment number) + (Comment Date -w/o time) (Comment User Name): (Comment with same format) <new line in between>	To be: Date of most recent comment
Comment 1: FirstName LastName :: FirstName.LastName@email.com	Comment 1: Invoice completed	Comment 1: 06-25-2020 12:27:40:457	1 06-25-2020 FirstName LastName: Invoice Completed	06-25-2020
Comment 1: FirstName LastName :: FirstName.LastName@email.com Comment 2: FirstName2 LastName2 :: FIRSTNAME2.LASTNAME2@EMAIL.COM	Comment 1: There are 5 incomplete invoices  Comment 2: Line 10 is now complete	Comment 1: 07-13-2020 08:22:29:487 Comment 2: 08-25-2020 06:37:38:717	1 07-13-2020 FirstName LastName: There are 5 incomplete invoices  2 08-25-2020 FirstName2 LastName2: Line 10 is now complete	8/25/2020

[fmvizcaino · Accepted solution]

Hi @rishipradeep10 ,

It took me a lot of tools to achieve your to be columns, but here it is.

I tried to comment each tool for you to understand what were the whole idea.

Best,

Fernando Vizcaino

[rishipradeep10]

Thank you so much for the solution as well as the commented explanations Fernando! It did exactly what I needed it to do. I also now understand the places where I was making mistakes and the tools I can employ to complete certain tasks.