Hi All, I have a table in the format as shown below. Every 3 rows contains registered information of a group of people. Depending on the number of people in a group, the number of column varies. (Original example from post by @aysha6029: https://community.alteryx.com/t5/Data-Preparation-Blending/Crosstab-records-based-on-column-names/m-p/55636#M10290) Field1Field2Field3Field4Name HikePeterLanceAddress GravetteLondonDCTelephone 444555666121131324242NameAlaric AddressANZ Telephone989876778 Name MikeRonald Address GraniteIndonesia Telephone 22288877798992 What I need is to transpose this table into the ideal format like shown below. ameAddressTelephoneHikeGravette 444555666PeterLondon1211313LanceDC24242AlaricANZ989876778MikeGranite 22288877RonaldIndonesia798992 As I am pretty new to Alteryx, hope someone can provide a detail explanation about how I can achieve this. Thanks. PANG

Transpose/Crosstab selected rows with different number of columns

Hi All,

I have a table in the format as shown below. Every 3 rows contains registered information of a group of people. Depending on the number of people in a group, the number of column varies. (Original example from post by @aysha6029: https://community.alteryx.com/t5/Data-Preparation-Blending/Crosstab-records-based-on-column-names/m-p/55636#M10290)

Field1	Field2	Field3	Field4
Name	Hike	Peter	Lance
Address	Gravette	London	DC
Telephone	444555666	1211313	24242
Name	Alaric
Address	ANZ
Telephone	989876778
Name	Mike	Ronald
Address	Granite	Indonesia
Telephone	22288877	798992

What I need is to transpose this table into the ideal format like shown below.

ame	Address	Telephone
Hike	Gravette	444555666
Peter	London	1211313
Lance	DC	24242
Alaric	ANZ	989876778
Mike	Granite	22288877
Ronald	Indonesia	798992

As I am pretty new to Alteryx, hope someone can provide a detail explanation about how I can achieve this.

Thanks.

PANG

Help

Accepted answers

JordanB

Hi @PANG

Interesting use case here. I have attached a workflow (V 11) with annotations describing the process.

Thanks to @NickC for his help!

Best,

Jordan Barker

Solutions Consultant

Transpose_Crosstab Example.yxmd

All comments

JordanB

Hi @PANG

Interesting use case here. I have attached a workflow (V 11) with annotations describing the process.

Thanks to @NickC for his help!

Best,

Jordan Barker

Solutions Consultant

Transpose_Crosstab Example.yxmd

PANG

Hi @JorddanB,

Thanks for the explanation. However I am a bit lost at the Summarize part.

After Transpose, my table looks like this:

Do you mind to elaborate a bit on the following steps?

Thanks,

PANG

Hi JordanB,

Thanks for the clarification. It worked well for the example case..

However, one more "challenge" from me is that I actually have a huge table ( about 50k names, with phone and address, and the list is getting longer daily), I haven't try the method out with all the data, but I am wondering if this method will work well, especially considering the use of Summarize tool to Concatenate all rows into single string.

What do you think?

Thanks,

PANG

Joe_Mako

One of the things to watch out for in data like this is blank cells, or missing rows. The solution from @JordanB makes the assumption that no cells will be blank, and no rows will be missing. For example, if we delete just the value "ANZ" or just that entire row, then the results will be incorrect.

Here is another approach that can handle blank values and missing rows. In this case, I am making the assumption that a Field1 value of "Name" starts a new set of records.

Transpose_Crosstab Example jm edit.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703