How do I pivot with repeating columns

[David_Mosk]

Below is what my data looks like. Attached is the full data set.

There are 3 possible job families - FIN, HR, IT

There are 2 possible Actions - TER, HIR

Then there is a KPI_Calc_Sum score.  Not all possible combinations exist for each Company.

I would like to pivot the data so each row is a unique company name with the fields being broken out as below where each field is a possible Job Family / Action combination which is 6 in total.

This is the original data format

Entity	Job Family	Action	KPI_Calc_Sum
Company_1	FIN	TER	0
Company_1	HR	TER	0
Company_2	HR	HIR	3
Company_2	HR	TER	2
Company_3	FIN	TER	2
Company_3	HR	HIR	0
Company_3	IT	HIR	0
Company_3	IT	TER	2
Company_4	HR	HIR	0
Company_5	HR	TER	2
Company_5	IT	HIR	0
Company_5	IT	TER	0

This is what I would like to do

Entity	FIN HIR	HR HIR	IT HIR	FIN TER	HR TER	IT TER
Company_1				0	0
Company_2		3			2
Company_3		0	0	2		2
Company_4		0
Company_5			0		2	0

Thanks!

[AbhilashR · Accepted solution]

Hi @David_Mosk, the Cross Tab tool helps you pivot the data and the config of the tool will look something like:

Given your pivoted column header was across multiple columns in your source file, as a first step we concatenate them using a Formula tool (Combined in my example), and then pivot it using a Cross Tab tool. I have attached a working example of my approach here.

Hope this helps and let us know if you need would like an additional explanation around the approach.

[David_Mosk · Accepted solution]

@AbhilashR Thanks! That's great!

I think there is one last step missing. I added a Summarize node as the last step grouping by Entity and then Summing the remaining fields. This makes each row represent a single Entity.  Attached is the tweaked workflow.