How to cross tab vertical columns to horrizontal rows

[Saravanan13]

Hello All,

I am looking to convert the below vertical columns to horizontal data as shown in output. Currently the output has two rows of data but might increase based on the input.

Can anyone assist.

Input -

Record no	Field	Value
47149220	value.0	present
47149220	id	signature
47149221	value.0	Application form
47149221	id	Form
47149222	value.0	October 2021
47149222	id	Revision
47149223	value.0	SINGAPORE
47149223	id	countryCitizenship
47149224	value.0	1/1/1900
47149224	id	dateOfBirth
47149225	value.0	John sena
47149225	id	fullName
47149226	value.0	NOT_SELECTED
47149226	id	6b
47149227	value.0	111111111H
47149227	id	ID
47149228	value.0	SINGAPORE 11111
47149228	id	City or town, state or province. Include postal code where appropriate.
47149229	value.0	SINGAPORE
47149229	id	countryOfResidence
471492210	value.0
471492210	id	mailingAddress
471492211	value.0	John sena
471492211	id	Print name
471492212	value.0
471492212	id	countryOfResidence
471492213	value.0
47994170	value.0	present
47994170	id	signature
47994171	value.0	Application form
47994171	id	Form
47994172	value.0	October 2021
47994172	id	Revision
47994173	value.0	JAPAN
47994173	id	countryCitizenship
47994174	value.0	1/1/1901
47994174	id	dateOfBirth
47994175	value.0	Richard Miller
47994175	id	fullName
47994176	value.0	NOT_SELECTED
47994176	id	6b
47994177	value.0	2222222H
47994177	id	ID
47994178	value.0	JAPAN 11111
47994178	id	City or town, state or province. Include postal code where appropriate.
47994179	value.0	JAPAN
47994179	id	countryOfResidence
479941710	value.0
479941710	id	mailingAddress
479941711	value.0	Richard Miller
479941711	id	Print name
479941712	value.0
479941712	id	countryOfResidence
479941713	value.0

Output -

signature

Form

Revision

countryCitizenship

dateOfBirth

fullName

6b

ID

City or town, state or province. Include postal code where appropriate.

countryOfResidence

mailingAddress

Print name

countryOfResidence

present

Application form

October 2021

SINGAPORE

1/1/1900

John sena

NOT_SELECTED

111111111H

SINGAPORE 11111

SINGAPORE

John sena

present

Application form

October 2021

JAPAN

1/1/1901

Richard Miller

NOT_SELECTED

2222222H

JAPAN 11111

JAPAN

Richard Miller

[DanielG]

@Saravanan13  - there is a cross tab tool in Alteryx for this purpose.  Its in the Transform Ribbon.

[Saravanan13]

Hello, I tried using but not able get in this format, Can you help with the workflow.

[jNunez]

Is this the incoming data? You're not giving us nearly enough to work with.There's probably a better point earlier on in the workflow to help.

If this is the structure of the whole dataset then you can use a record id tool, start it at 0. Add a formula tool with one field containing Mod("# New Record ID Field", 24). This field will show which record in the horizontal table each row will belong to. You're dividing by 24 because there's 12 columns and each column has a field name and value cell. 

Then you'll filter the data stream by field = 'id', then join the true output to the false output anchor with join keys  = ('Record No.", and Field you created in formula Tool).

[Saravanan13]

Hello, Yes this is a incoming data. So 24 columns is not a fixed number. It might change depending on the input.

[binuacs]

@Saravanan13 One way of doing this

[Saravanan13]

Thank you so much. I have one problem. the order in my original dataset is not uniform. For example rows in value column will not be in same order. It will differ for each record ID. What should be workaround for this.

[binuacs]

@Saravanan13 Can you provide a sample file with different order of values, without seeing the input file it is difficult to come to a solution

[OllieClarke · Accepted solution]

Hi @Saravanan13 in this instance one approach is to use a batch macro:



You can then deal with each row of data independently.

I calculate this row by taking the first 7 digits and splitting them out:


I group by this in the summarise tool to create the batches, and then use the row field to group by the batch macro too.


The batch macro itself just performs the cross-tab and rename, but in the interface designer properties, you can set to auto-configure by name, so this will align all headers after the macro has run.

Hope that helps,

Ollie

[Saravanan13]

Thank you so much.. it worked.