Data Manipulation using cross tab and transpose

EoinM824

Working on data that looks like this

EE ID	Cost Centre	Period	SalaryA	SalaryB	BonusA	BonusB	Pension
1	AA	Jan	1000		100
2	AA	Jan	2000		200
3	BB	Jan		1500		150	100
7	BB	Jan		2500		250

EE ID	Cost Centre	Period	SalaryA	SalaryB	BonusA	BonusB	Pension
1	AA	feb	1000		100
2	AA	feb	2000		200
3	BB	feb		1500		150	100
9	BB	feb		4500		2000

EE ID	Cost Centre	Period	SalaryA	SalaryB	BonusA	BonusB	Pension
2	AA	mar	2000		200
3	BB	mar		1500		150	100
9	BB	mar		4500		2000

Need it to look something like this:

EE ID	Cost Centre	Period	SalaryA	SalaryB	BonusA	BonusB	Pension	Period	SalaryA	SalaryB	BonusA	BonusB	Pension	Period	SalaryA	SalaryB	BonusA	BonusB	Pension
1	AA	Jan	1000		100			feb	1000		100			mar
2	AA	Jan	2000		200			feb	2000		200			mar	2000		200
3	BB	Jan		1500		150	100	feb						mar		1500		150	100
7	BB	Jan		2500		250		feb		1500		150	100	mar
9	BB	Jan						feb		4500		2000		mar		4500		2000

Is this possible? I can join the input tables using the union tool, I have transposed using EE ID and Cost Centre as key fields and then cross tabbed again with EE ID and Cost Centre as key fields. This giving me the desired number of rows.Fields EE ID and Cost centre are populated. Struggling to figure out how to manipulate the remaining fields for desired output. any help would be great.

Current attempt attached below.

Test Workflow - PD.yxmd

fmvizcaino

Hi @EoinM824 ,

Attached is an example showing how to do it. I'm not sure if it is the simple way, but it does the job.

Let me know if that works for you.

Best,

Fernando V.

Data Manipulation using cross tab and transpose.yxmd