Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Regex or Text to Columns for varying input data lengths.

JFurda
8 - Asteroid

I've been scratching my head how to solve this one. I have a report that contains a column with job position information. The field can contain up to 100 positions in each cell. I want to parse them out to their own columns. The only consistent thing about each new position is that it starts with a "P" and an 8 digit number... oh and line breaks (see below)
Here's a de-identified sample of what could be in a cell: 

 

Position

P01234567 OB-PA (Unfilled)

P12345678 Physician - Radiation Oncologist-7 (Closed)

 

P12345679 Physician - Joe Smith

 

P12345680 Physician - Radiation Oncologist-2 (Unfilled)

P40000008 Student Pharmacist - Rotation -5 (Closed)

 

P40000007 Student Pharmacist - Rotation -4 (Closed)

 

P40000006 Student Pharmacist - Rotation -3 (Closed)

 

P40000005 Student Pharmacist - Rotation (Unfilled)

 

P40000004 Student Pharmacist - Rotation (Unfilled)

 

 

Yes, there are two line breaks in between the data. I can remove them using the cleanse tool or use them to my advantage
Are there any solutions that anyone can think of that could account for 1 to 100 of these in a single cell and parse them out (ultimately) to their own columns in the format of: 

 

Position 1Position 2Position 3Position 4Position N
P01234567 OB-PA (Unfilled)nullnullnullnull
P12345678 Physician - Radiation Oncologist-7 (Closed)P12345679 Physician - Joe SmithP12345680 Physician - Radiation Oncologist-2 (Unfilled)nullnull

 

RegEx and parse will only allow me to do so many. 

 

Thanks! 

2 REPLIES 2
Qiu
20 - Arcturus
20 - Arcturus

@JFurda 
If I understand you intention correctly

0304-JFurda.PNG

JFurda
8 - Asteroid

@Qiu That's nearly it! My incoming data has two line breaks (a blank line between each line of data) I threw a filter between the Text to Rows and Tile tools to keep any entries where !IsEmpty([Position]) . 

 

Many, many thanks for the efficient solution! I can even join my data back to the remainder of the report based on the RecordID. 

Labels