topic Re: RANK TABLE in QlikView

RANK TABLE

eva_maria — Wed, 25 Nov 2020 16:16:04 GMT

Hi!

From the table that i put in my question file, i need to create tables like that, where numbers are the ranking depending on the average value. If the average is the same, the rank will be the same.

Thanks for your help.

table 1 only with pto
pto	Q1_E	Q2_E	Q3_E	Q4_E	Q1_J	Q2_J	Q3_J	Q4_J
COM	1	2	1	1	3	2	1	2
DIR	2	3	2	1	2	3	1	2
JCDTOS	1	1	2	3	2	1	3	4
SUBD	2	2	1	3	2	2	1	2

table 2 with pto and zone
pto_zon	Q1_E	Q2_E	Q3_E	Q4_E	Q1_J	Q2_J	Q3_J	Q4_J
COM_G	1	2	1	1	1	2	1	2
COM_S	1	1	1	1	2	1	1	1
DIR_G	2	2	1	1	2	3	1	2
DIR_M	1	1	1	1	2	2	1	2
DIR_S	2	3	4	1	1	1	1	1

Re: RANK TABLE

eva_maria — Tue, 19 Jun 2018 18:47:27 GMT

Sorry i've attached the wrong file.

This is the real data table.

Regards

Re: RANK TABLE

neelamsaroha157 — Tue, 19 Jun 2018 20:23:46 GMT

You want your data to look like this -

If yes, then try rank function.

Re: RANK TABLE

eva_maria — Wed, 20 Jun 2018 07:49:42 GMT

Hi Neelam!

Thanks for your reply.

Using rank fuction as you do in your table, sets the rankin by columns (Pto field) and I need it by rows and making difference between the questions for the employee (_E) and their boss (_J).

As you can see in my question file, for Pto COM, depending on the avg value of the employee answers, Q1_E,Q3_E and Q4_E have the same value so the rank has to be 1. Question Q2_E average is lower than the other so rank has to be 2.

For the boss, Q3_J has the highest average so rank is 1. Q2_J and Q4_J are next so rank for both is 2. Finally Q_J has the lower average so rank is 3.

Regards

Re: RANK TABLE

Frank_Hartmann — Wed, 20 Jun 2018 11:48:13 GMT

Is this what you are looking for?

Re: RANK TABLE

eva_maria — Wed, 20 Jun 2018 12:16:56 GMT

Hi Frank!

Thanks for your reply, is almost perfect. just missing to calculate separately the ranking of the questions of the employee (Q*_E) of those of the boss (Q*_J).

Regards

Re: RANK TABLE

Frank_Hartmann — Wed, 20 Jun 2018 12:39:30 GMT

like this?

Re: RANK TABLE

eva_maria — Wed, 20 Jun 2018 12:55:10 GMT

Perfect!!

How did you do it??

Re: RANK TABLE

Frank_Hartmann — Wed, 20 Jun 2018 12:56:20 GMT

pivot table:

dim1 = pto

dim2 = valuelist('Q1_E','Q2_E','Q3_E','Q4_E','Q1_J','Q2_J','Q3_J','Q4_J')

Expression =

if(match( valuelist('Q1_E','Q2_E','Q3_E','Q4_E','Q1_J','Q2_J','Q3_J','Q4_J'),'Q1_E','Q2_E','Q3_E','Q4_E'),

hrank(pick(match(valuelist('Q1_E','Q2_E','Q3_E','Q4_E','Q1_J','Q2_J','Q3_J','Q4_J'),'Q1_E','Q2_E','Q3_E','Q4_E'),

avg(E_I1),avg(E_I2),avg(E_I3),avg(E_I4),)),

hrank(pick(match(valuelist('Q1_E','Q2_E','Q3_E','Q4_E','Q1_J','Q2_J','Q3_J','Q4_J'),'Q1_J','Q2_J','Q3_J','Q4_J'),

avg(J_I1),avg(J_I2),avg(J_I3),avg(J_I4),)))

then drag the second dimension to the upper right corner untill you see a blue horizontal line

Re: RANK TABLE

eva_maria — Wed, 20 Jun 2018 13:22:03 GMT

Thanks for your invaluable help and for your time!!

Regards

Re: RANK TABLE

Frank_Hartmann — Thu, 21 Jun 2018 09:38:24 GMT

Did you manage it?

Re: RANK TABLE

eva_maria — Thu, 21 Jun 2018 10:38:12 GMT

Yes, something goes wrong with my qlikview, just changing hrank for rank and then again, just works fine.

Thanks!!