Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

DATA PREPARATION | Append to Rows (for lack of a better description)

Highlighted
8 - Asteroid

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.

Highlighted
Alteryx
Alteryx

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

 

 

Append_to_Rows.png

 

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

Highlighted
8 - Asteroid

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

 

Highlighted
8 - Asteroid

@KaneG thank you so much. I was able to get the workflow open. I applied your flow and it worked great!

Labels