Data Transpose

[hemant86]

Hi Team I have data in the below format and need output in the give format below. Appreciate any help around this.

Input Data:

EMP ID	File#	Paygroup	Gross Amount	Loc Code	Rehire Date	Termination Date
346	21261	W3K	0	6089	6/20/2016	3/15/2023
346	253906	PAV	4963.11	1320	6/20/2016	3/15/2023
346	318683	WXB	1117.22	1320	6/20/2016	3/15/2023
814	2180	TZL	222,112.19	VNM	2/20/2012
814	1037	JHZ	144789	VCA	2/20/2012

Output Data:

Employee ID	Paygroup 1	Gross amount 1	Loc 1	Paygroup 2	Gross amount 2	Loc 2	Paygroup 3	Gross amount 3	Loc 3
346	W3K	0	6089	PAV	4963.11	1320	WXB	1117.22	1320
814	TZL	222,112.19	VNM	JHZ	144789

The Employee ID header is fixed but the tool should be able to create maximum number of columns dynamically. Like in this input we 3 entries for one employee and 2 entries for the other so the tool would create Paygroup1 , Paygroup2 , Paygroup3 . Similarly Gross amount 1 , Gross amount 2 , Gross amount 3 & Loc 1 , Loc 2 , Loc 3. The columns File# & Termination Date can be ignored.

Thanks in advance!

[cjaneczko · Accepted solution]

Here is one way to do it.

[hemant86]

Thanks @cjaneczko . This works great. I learned something new. 😊

[cjaneczko]

Great. If the number of columns became larger you could add a Field Info tool to the workflow as well as a Dynamic Rename to dynamically set the field order. Right now, if more fields get added you need to rearrange it with the Multi-Join tool.