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.

[fmvizcaino]

Hi @EoinM824 ,

In this particular case, you can use the multiple join, it does exactly what you are looking for.

Look at the example and let me know if that works for you

Best,

Fernando V.

[EoinM824]

Thanks @fmvizcaino 

I was exploring this earlier - my issue is that EE ID 9 from the 3rd table does not join the output as it is unique. Would also need the period columns populated if null. I was trying to use a multirow formula but it seems a little tedious!

Eoin

[RolandSchubert]

Hi @EoinM824 ,

I created a workflow using Join Multiple, added a Formula tool to fill NULL values for EE ID and Cost Centre and a Summarize tool to identify the periods, added the using Append Fields then. Does this solve your problem?

Best regards

Roland

[fmvizcaino · Accepted solution]

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.