community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to parse specific multiple rows in one column to multiple columns

Highlighted

Hello, I am new to Alteryx so I will try to describe my issue as best I can. I have a text file which I pasted onto excel to obtain the following:

 

Revision: 23251
Author: John Doe
Date: Monday, May 7, 2018 5:56:22 PM
Message:
Seller change 5/7/2018:
Message 1: change made to seller name


This is also part of the message column.


----
Modified : Folder 1
Modified : Folder 1/File 2
Modified : Folder 1/File 3

 

Revision: 23243
Author: Jane Doe
Date: Thursday, May 3, 2018 3:30:10 PM
Message:
Customer change 5/3/2018:
Message 1: Customer request
- update Address to New York, NY 10002


This is also part of the message.

----
Modified : Folder 1
Modified : Folder 1/File 1

 

I would like Alteryx to assist in helping me obtain an output of something like the below table, but I can't seem to figure out how using delimiters/cross tabs, etc. just because there seem to be multiple variables within the list. Thanks in advance for your help!

 

RevisionAuthorDateMessageModified_1Modifed_2Modified_3
23251John DoeMonday, May 7, 2018 5:56:22 PM

Seller change 5/7/2018: 
Message 1: change made to seller name


This is also part of the message column.

Folder 1Folder 1/File 2Folder 1/File 3
23243Jane DoeThursday, May 3, 2018 3:30:10 PM

Customer change 5/3/2018: 
Message 1: Customer request
- update Address to New York, NY 10002


This is also part of the message.

Folder 1Folder 1/File 1 

 

Edit: Uploaded test file in excel.

Quasar

Hi @snargaluff

 

I found this regex string to work:

Modified\s:\s(.*)\nModified\s:\s(.*)\nModified\s:\s(.*)

A great place to test and try out regex is regex101.com

 

An example is attached.

 

Kind regards

Kat

Cool, thanks so much for the tip! 

 

Would you also happen to know how to do this for multiple records? I have hundreds of revisions to parse and the regex code seems to only be doing it for the first record?

Or maybe I'm misunderstanding something? It also only does it for the "Modified" files.

Quasar

@snargaluff Sorry, looks like I didn't read properly.

 

 

I'd suggest playing around a bit with different bits of regex. What can often be quite good is to split everything on new line (\n) into rows (this might mess up you message field though - you could join it back up with a multi-row formula) and then dealing with the different parts separately. In the end you can crosstab your data back into columns.

Labels