topic Re: Pivot Table Help in QlikView

Pivot Table Help

prees959 — Fri, 26 Oct 2018 08:33:50 GMT

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

Re: Pivot Table Help

sunny_talwar — Fri, 26 Oct 2018 11:57:46 GMT

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

Re: Pivot Table Help

prees959 — Wed, 31 Oct 2018 17:21:53 GMT

Thanks ! Perfect