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!
Solved! Go to 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.