Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
prees959
Creator II
Creator II

Pivot Table Help

Hi,

I have the following sample dataset :

     

DateStaff Type AStaff Type BStaff Type CStaff Type C
01/01/20181554
01/01/20183626
01/01/20183367
01/01/20185458
01/01/20187473
01/01/20182446
01/01/20186767
01/02/20184778
01/02/20187249
01/02/20182334
01/02/20187663
01/02/20184777
01/02/20187888

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

   

Type01/01/201801/02/2018
Staff Type A2731
Staff Type B3333
Staff Type C3535
Staff Type D4139

Any help appreciated..

Thanks,

Phil

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

2 Replies
sunny_talwar

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

Capture.PNG

prees959
Creator II
Creator II
Author

Thanks ! Perfect