How do I pivot with repeating columns

Question

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

EntityJob FamilyActionKPI_Calc_SumCompany_1FINTER0Company_1HRTER0Company_2HRHIR3Company_2HRTER2Company_3FINTER2Company_3HRHIR0Company_3ITHIR0Company_3ITTER2Company_4HRHIR0Company_5HRTER2Company_5ITHIR0Company_5ITTER0

This is what I would like to do

EntityFIN HIRHR HIRIT HIRFIN TERHR TERIT TERCompany_1   00 Company_2 3  2 Company_3 002 2Company_4 0    Company_5  0 20

Thanks!

comp data example.xlsx

AbhilashR · Accepted Answer

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 Answer

@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.