topic Re: Pivot table question in QlikView

Pivot table question

Fri, 31 Aug 2012 20:07:44 GMT

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.

Re: Pivot table question

Sat, 01 Sep 2012 11:03:31 GMT

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;

Re: Pivot table question

sebastiandperei — Sat, 01 Sep 2012 14:59:02 GMT

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))

Re: Pivot table question

Tue, 04 Sep 2012 02:22:06 GMT

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..

Re: Pivot table question

sebastiandperei — Tue, 04 Sep 2012 04:55:21 GMT

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