I need to move some rows of data into columns.
Note: All of the rows are currently in one column. I will eventually use the Text-to-Column tool using the space delimiter to break it out into columns.
THIS IS THE SOURCE DATA:
LOCATION-123456 Period-122612-010813 Page-1
6789 6789 JANE Q PUBLIC 9010
TI-A 122812 1105 BK-A 122812 1442
TI-A 122912 1600 TO-A 123012 0126
TI-A 123012 1701 TO-A 123112 0004
0123 5678 JOHN SMITH 9010
TI-A 122612 0930 BK-A 122612 1118
TI-A 122612 1430 BK-A 122612 1544
TI-A 122712 0933 BK-A 122712 1107
TI-A 122712 1440 BK-A 122712 1617
LOCATION-234567 Period-122612-010813 Page-2
4567 4567 JILL Z PRIVATE 9010
TI-A 122812 1105 BK-A 122812 1442
TI-A 122912 1600 TO-A 123012 0126
HOW I WOULD LIKE THE DATA TO BE:
LOCATION-123456 Period-122612-010813 Page-1 6789 6789 JANE Q PUBLIC 9010 TI-A 122812 1105 BK-A 122812 1442
LOCATION-123456 Period-122612-010813 Page-1 6789 6789 JANE Q PUBLIC 9010 TI-A 122912 1600 TO-A 123012 0126
LOCATION-123456 Period-122612-010813 Page-1 6789 6789 JANE Q PUBLIC 9010 TI-A 123012 1701 TO-A 123112 0004
LOCATION-123456 Period-122612-010813 Page-1 0123 5678 JOHN SMITH 9010 TI-A 122612 0930 BK-A 122612 1118
LOCATION-123456 Period-122612-010813 Page-1 0123 5678 JOHN SMITH 9010 TI-A 122612 1430 BK-A 122612 1544
LOCATION-123456 Period-122612-010813 Page-1 0123 5678 JOHN SMITH 9010 TI-A 122712 0933 BK-A 122712 1107
LOCATION-123456 Period-122612-010813 Page-1 0123 5678 JOHN SMITH 9010 TI-A 122712 1440 BK-A 122712 1617
LOCATION-234567 Period-122612-010813 Page-2 4567 4567 JILL Z PRIVATE 9010 TI-A 122812 1105 BK-A 122812 1442
LOCATION-234567 Period-122612-010813 Page-2 4567 4567 JILL Z PRIVATE 9010 TI-A 122912 1600 TO-A 123012 0126
Thanks for any guidance you can provide.
Solved! Go to Solution.
Hi @JBO,
In this, you will need to identify each row first. From that point, there's probably 10 different ways to solve it, but I find it's easiest to just split and join.
So, a Multi-row to identify the top grouping (Field: __Row1__):
IF StartsWith([Field1], 'LOCATION')
THEN [Row-1:__Row1__]+1
ELSE [Row-1:__Row1__]
ENDIF
And then another to identify the next grouping (Field: __Row2__):
IF REGEX_Match(LEFT([Field1],9),'\d{4} \d{4}')
THEN [Row-1:__Row2__]+1
ELSE [Row-1:__Row2__]
ENDIF
2 filters, 1 with StartsWith([Field1], 'LOCATION') , the other with REGEX_Match(LEFT([Field1],9),'\d{4} \d{4}')
And then join the main records (false from the 2nd filter) to the other 2 columns
I've included a couple of other methods that I quickly thought of while building the above.
Also, another tip in this kind of operation is a "RecordID with Grouping" is just a Multi-Row with Grouping: [Row-1:Field1]+1
Thank you @KaneG. I can't open the sample workflow you sent. Even though I am running 11.0.6.28907, it's telling me the flow was created with a more current version of Alteryx. Not sure why.
I'll see what I can do with what you set forth.
Thanks for taking the time.
-Jen
@KaneG thank you so much. I was able to get the workflow open. I applied your flow and it worked great!