Configuring cross-tab/transpose combo

[LouisJ]

Hello guys, I'm having trouble configuring a transpose-crosstab combo, for the following data :

To be clear, there is one "key column" (State) and 4 value columns. I would like to reorganise this into 8 value columns (Pause_Quantity, Pause_Total duration, ..., Active_Quantity, .. etc)

I've managed to get to this point :

But cannot seem to configure a cross-tab correctly afterwards to end up with 8 columns properly sorted.

I have shared a "dummy" excel sheet with the data like in the first picture, any help to try and make it into 8 columns would be great !

Thanks in advance,

LJ

[Samantha_Jayne]

You are there, you just need to cross tab back using your new header (column names) to get the details the way you need it. I always refer to the examples of Cross Tab and transpose to help other learn how they work. If you haven't yet seen this, click on the tool, in the tool palette and "Open Example".

The issue you may have is you are missing a key to help you identify which of the active and pause rows equal. Once you have this then you will be able to cross tab back into what you need straight away. Hope this helps you on your way. S*

[Qiu · Accepted solution]

@LouisJ 
Maybe you are just missing a Record ID tool?

[DavidSkaife · Accepted solution]

Hi @LouisJ 

Almost there, a RecordID tool allows you to group by in the final Cross Tab:

[LouisJ]

Yes, thank you all, that's exactly it !

Record ID did the trick, in hindsight that makes total sense.

Have a nice day,

LJ

Screenshot of cross-tab config for any people in the future having this issue :