Pivot Table Help

[prees959]

Hi,

I have the following sample dataset :

Date	Staff Type A	Staff Type B	Staff Type C	Staff Type C
01/01/2018	1	5	5	4
01/01/2018	3	6	2	6
01/01/2018	3	3	6	7
01/01/2018	5	4	5	8
01/01/2018	7	4	7	3
01/01/2018	2	4	4	6
01/01/2018	6	7	6	7
01/02/2018	4	7	7	8
01/02/2018	7	2	4	9
01/02/2018	2	3	3	4
01/02/2018	7	6	6	3
01/02/2018	4	7	7	7
01/02/2018	7	8	8	8

I need to be able to transpose this table into a pivot or straight table to show the following summary :

Type	01/01/2018	01/02/2018
Staff Type A	27	31
Staff Type B	33	33
Staff Type C	35	35
Staff Type D	41	39

Any help appreciated..

Thanks,

Phil

[sunny_talwar · Accepted solution]

The first step would be to use a crosstable load in the script to transform your data

Table:

CrossTable(StaffType, StaffValue)

LOAD * INLINE [

    Date, Staff Type A, Staff Type B, Staff Type C, Staff Type D

    01/01/2018, 1, 5, 5, 4

    01/01/2018, 3, 6, 2, 6

    01/01/2018, 3, 3, 6, 7

    01/01/2018, 5, 4, 5, 8

    01/01/2018, 7, 4, 7, 3

    01/01/2018, 2, 4, 4, 6

    01/01/2018, 6, 7, 6, 7

    01/02/2018, 4, 7, 7, 8

    01/02/2018, 7, 2, 4, 9

    01/02/2018, 2, 3, 3, 4

    01/02/2018, 7, 6, 6, 3

    01/02/2018, 4, 7, 7, 7

    01/02/2018, 7, 8, 8, 8

];

and then next you can create a pivot table with StaffType as your first dimension and Date as your second dimension (which is pivoted to the top) to get this

[prees959]

Thanks ! Perfect