Pivot table question

[#1]

Hi All,

My requirement is I have data

Customer Grade 1 Grade 2 Grade 3

A               -3          -2          1

B               -4          2           3  

C                0          -1          4

D                -3         -2          1

I need to have a pivot table as below based on count of customer

                   -4     -3     -2     -1     0     1     2     3     4

Grade 1         1     2                     1                             

Grade 2                         2     1                  1

Grade 3                                               2            1     1

Thank You.

[#2]

Hi,

This can be done using CrossTable, check the attached app.

T_Grade:

LOAD * Inline [
Customer, Grade1, Grade2, Grade3
A,-3,-2,1
B,-4,2,3
C,0,-1,4
D,-3,-2,1
];


T_Cross:
CrossTable(GradeNo, Grade, 1)
LOAD Customer,
     Grade1,
     Grade2,
     Grade3
Resident T_Grade;

DROP Table T_Grade;

[sebastiandperei · Accepted solution]

Hi,

If you know the max or min values that the grade can be (-4 to 4 in your example)

Dimention (calculated):

=ValueLooop(-4,4)

Expressions:

Grade1     =Count(If(ValueLooop(-4,4)=Grade1, Customer))

Grade2     =Count(If(ValueLooop(-4,4)=Grade2, Customer))

Grade3     =Count(If(ValueLooop(-4,4)=Grade3, Customer))

[#4]

Hi Sebastian Pereira

Your solution worked perfecectly. Thank You. But to keep my problem worst I need something to add..

if I need to show range -3 to 3 that is skipping grades -4 and 4 my requirement is now count of -3 grades is count -4 grades and -3 grades similarly count of 3 grades is count of 3 grades + count of 4 grades...

                   -4     -3     -2     -1     0     1     2     3     4

Grade 1         1     2                     1                            

Grade 2                         2     1                  1

Grade 3                                               2            1     1

so now my new structure is

                      -3     -2     -1     0     1     2     3    

Grade 1          3                     1                            

Grade 2                         2     1                  1

Grade 3                                               2     2    

Please let me know if we can do this..thanks a ton for your help..

[sebastiandperei]

Hi qlik! (do you have a name?)

Try the following, and tell me if it works (because i didn't tested)

Dimention (calculated):

=ValueLooop(-3,3)

Expressions:

Grade1     =Count(If(     ValueLooop(-3,3)=Grade1 or

                                          (ValueLooop(-3,3)=-3 and Grade1=-4) or

                                          (ValueLooop(-3,3)=3 and Grade1=4)

                                                     , Customer))

The same for the other expressions, but Grade2 and Grade3 instead of Grade1