Transpose/Crosstab selected rows with different number of columns

[PANG]

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

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

[JordanB · Accepted solution]

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

[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

[JordanB]

Hi @PANG

I needed to get all the NAME etc. values on one row so they I could transpose them back into one column.

If I didn't do this step there would be no way of putting them all in one field per category.

Best,

Jordan

[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

[JordanB]

Hi @PANG

Yes I expect this to work for the full data set :)

Best,

Jordan

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

[WiktorKitzmanVizyble]

Hi, I hope somebody is in this thread stii?

I am trying to do the same but bit more complicated i have more rows fields in column1 the rest is the same. 

Does anyone know why using previous method I am getting mismatched results? I ve tried to conigure it differently.

Instead of Name, Adress, Telephone I can have Company, CEO, Adress, Postal Code  or relations and many more....

but I download it with API so the set is big and sometimes a company missing TAX ID or CEO and this method did not work? do you know why?you newer know when some row is missing.

If somebody could help I could show you how to configure API workflow with authorization and formula to query different fields plus nice filter in return:)

Please help