Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
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