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

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

JBO
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.

3 REPLIES 3
KaneG
Alteryx Alumni (Retired)

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

JBO
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

 

JBO
8 - Asteroid

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

Labels