Union Tool/Stacking Data

[reginawhelan]

Hi, I would like to stack data that is within one data source. I would like for the Names and DOB's be stacked into 3 columns instead of 9 with the Identifier carried over for each person. The following is an example of the current and desired output:

Current
Identifier	Person #1 First Name	Person # 1 Last Name	Person #1 DOB	Person #2 First Name	Person # 2 Last Name	Person #2 DOB	Person #3 First Name	Person # 3 Last Name	Person #3 DOB
123	Name1	Last Name1	1/10/1978	Name5	Last Name5	1/10/1978	Name9	Last Name9	1/10/1978
124	Name2	Last Name2	1/11/1978	Name6	Last Name6	1/11/1978	Name10	Last Name10	1/11/1978
125	Name3	Last Name3	1/12/1978	Name7	Last Name7	1/12/1978	Name11	Last Name11	1/12/1978
126	Name4	Last Name4	1/13/1978	Name8	Last Name8	1/13/1978	Name12	Last Name12	1/13/1978
Desired
Identifier	First Name	Last Name	DOB
123	Name1	Last Name1	1/10/1978
124	Name2	Last Name2	1/11/1978
125	Name3	Last Name3	1/12/1978
126	Name4	Last Name4	1/13/1978
123	Name5	Last Name5	1/10/1978
124	Name6	Last Name6	1/11/1978
125	Name7	Last Name7	1/12/1978
126	Name8	Last Name8	1/13/1978
123	Name9	Last Name9	1/10/1978
124	Name10	Last Name10	1/11/1978
125	Name11	Last Name11	1/12/1978
126	Name12	Last Name12	1/13/1978

[JosephSerpis]

Hi @reginawhelan I mocked up an approach to tackle this. Let me know what you think?

[reginawhelan]

Thank you so much! I think this will work, I just need some help with the RegEx formula. The titles in my example were not the actual titles. The Actual titles are:

Spouse Information - First Name

F44 - Last Name

F45 - DOB

F46 - Gender

Dependent Child 1 - First Name

F48 - Last Name

F49 - DOB

F50 - Gender

F51 - Other Info

Dependent Child 2 - First Name

F53 - Last Name

F54 - DOB

F55 - Gender

F56 - Other Info

etc... 

Can you help?

[reginawhelan]

Sorry. I want to mention that the titles are just the data before the dash, for example (Spouse Information, F44, F45, F46). the data after the dash is what the information is.

Spouse Information - First Name

F44 - Last Name

F45 - DOB

F46 - Gender

Dependent Child 1 - First Name

F48 - Last Name

F49 - DOB

F50 - Gender

F51 - Other Info

Dependent Child 2 - First Name

F53 - Last Name

F54 - DOB

F55 - Gender

F56 - Other Info

[JosephSerpis]

Hi @reginawhelan can you provide a mock example file with how the columns name appear exactly in your data would be easier to work with.

[reginawhelan]

Thanks again for you time and help. The attached spreadsheet includes the actual titles. I should also mention that sometimes cells are blank and that's ok.

[Ben_H]

Hi @reginawhelan,

I've attached a potential solution for you. I can't help but feel it's a little over complicated but it seems to meet your requirements.

Be sure to read the included commentary.

Regards,

Ben

[reginawhelan]

Hi, unfortunately I can't open your example as it is in a newer version of Alteryx. Is there a way you can send it in Alteryx Designer x64?

[JosephSerpis · Accepted solution]

Hi I amended the workflow using your sample data you provided. Let me know what you think?

[Ben_H]

Hi @reginawhelan,

Just in case you run into the version issue in future -

If you right click on the yxmd file and open with notepad you will see the xml for the routine.

At the top you'll see "<AlteryxDocument yxmdVer=..." If you change the value in quotes from 2020.2 to match your version of alteryx and then save you should then be able to open the file.

Regards,

Ben