topic Re: how to get the consecutive rank when there are same ranks in column using rank function in QlikView

how to get the consecutive rank when there are same ranks in column using rank function

fashid — Sat, 02 May 2015 19:58:26 GMT

Hi,

I have a table which has a rank function in it .

two rows have the same rank 8.

After that there is rank 10 but i need that to be 9 and the last one to be 10.

Could it be possible.

Regards,

Nadeem

Re: how to get the consecutive rank when there are same ranks in column using rank function

Anonymous — Sat, 02 May 2015 21:30:57 GMT

See attached

Re: how to get the consecutive rank when there are same ranks in column using rank function

sunny_talwar — Sat, 02 May 2015 23:33:15 GMT

Michael‌‌ I think he is looking for this:

Branch	Total Score	Rank
PUN	44	1
MUM	40	2
BLR	36	3
CAL	35	4
DEL	31	5
CHN	28	6
GOA	26	7
AHD	25	8
HYD	25	8
COC	15	9
CHD	0	10

Is this possible? Tried it doing using different options with Rank but couldn't get it to work.

Sunny

Re: how to get the consecutive rank when there are same ranks in column using rank function

Anonymous — Sun, 03 May 2015 00:02:10 GMT

It is possible but quite cumbersome... Attached. Maybe you find a better way...

Re: how to get the consecutive rank when there are same ranks in column using rank function

sunny_talwar — Sun, 03 May 2015 00:07:53 GMT

May be this if doing in in the script is okay

Temp:

LOAD * INLINE [

Branch, Total Score

PUN, 44

MUM, 40

BLR, 36

CAL, 35

DEL, 31

CHN, 28

GOA, 26

AHD, 25

HYD, 25

COC, 15

CHD, 0

];

Table:

NoConcatenate

LOAD *

Resident Temp

Order By [Total Score] desc;

Join (Table)

LOAD Distinct [Total Score],

AutoNumber([Total Score]) as Rank

Resident Table;

DROP Tables Temp;

Re: how to get the consecutive rank when there are same ranks in column using rank function

nagaiank — Sun, 03 May 2015 00:16:56 GMT

The following expression in Michael's solution seems to give the desired result.

RowNo(total)-

rangesum(above(total

if(num(rank(total Score,4))=above(num(rank(total Score,4))),1,0),

0,RowNo(total)))

Re: how to get the consecutive rank when there are same ranks in column using rank function

fashid — Sun, 03 May 2015 05:51:47 GMT

Yes Sunny i am looking for the same output

Re: how to get the consecutive rank when there are same ranks in column using rank function

fashid — Sun, 03 May 2015 05:58:53 GMT

Hi Michael,

Thanks for replying .

You example is giving me 9th rank for HYD . I want AHD and HYD to be both at the same rank that is 8th.

The 9th rank should be given to COH.

I find this very weird in qlikview .

Regards

Nadeem

Re: how to get the consecutive rank when there are same ranks in column using rank function

sunny_talwar — Sun, 03 May 2015 11:08:27 GMT

Did you try the following script?

Temp:

LOAD * INLINE [

Branch, Total Score

PUN, 44

MUM, 40

BLR, 36

CAL, 35

DEL, 31

CHN, 28

GOA, 26

AHD, 25

HYD, 25

COC, 15

CHD, 0

];

Table:

NoConcatenate

LOAD *

Resident Temp

Order By [Total Score] desc;

Join (Table)

LOAD Distinct [Total Score],

AutoNumber([Total Score]) as Rank

Resident Table;

DROP Tables Temp;

Re: how to get the consecutive rank when there are same ranks in column using rank function

fashid — Sun, 03 May 2015 11:35:47 GMT

I cannot do this way because the scores are calculated in front end itself

Re: how to get the consecutive rank when there are same ranks in column using rank function

sunny_talwar — Sun, 03 May 2015 11:38:19 GMT

Then look at Michael's second solution.

Re: how to get the consecutive rank when there are same ranks in column using rank function

Anonymous — Sun, 03 May 2015 15:31:31 GMT

That's exactly what my second version is doing

Re: how to get the consecutive rank when there are same ranks in column using rank function

fashid — Sun, 03 May 2015 18:25:11 GMT

Hi Michael,

It was cool solution thanks

I used

=RowNo(total)-rangesum(above(total if(num(rank(total Column(1),1))=above(num(rank(total Column(1),1))),1,0),0,RowNo(total)))

as the score was being calculated in the chart .

Difficult to understand the expression through .

Many thanks to everybody helping me in this solution.

Regards

Nadeem

Re: how to get the consecutive rank when there are same ranks in column using rank function

fashid — Mon, 04 May 2015 14:12:00 GMT

Hi Michael,

I applied the logic in another chart but i am getting wrong ranks.

In the charts with the caption ENQ others and ENQ 4 SL i am getting wrong results.

In the charts with the caption conversion and CFR i am getting correct rank

I have used the same logic.but somehow unable to get the desired result

Could you please help me on this .

Regards,

Nadeem

Re: how to get the consecutive rank when there are same ranks in column using rank function

sunny_talwar — Mon, 04 May 2015 14:27:24 GMT

Here is your potential issue (0s are not actual 0s)

Re: how to get the consecutive rank when there are same ranks in column using rank function

fashid — Mon, 04 May 2015 18:34:35 GMT

Ok Many Thanks friend I used the round function to sort it.

Re: how to get the consecutive rank when there are same ranks in column using rank function

sunny_talwar — Mon, 04 May 2015 18:36:52 GMT

Awesome

Branch	Total Score	Rank
PUN	44	1
MUM	40	2
BLR	36	3
CAL	35	4
DEL	31	5
CHN	28	6
GOA	26	7
AHD	25	8
HYD	25	8
COC	15	9
CHD	0	10

Branch	Total Score	Rank
PUN	44	1
MUM	40	2
BLR	36	3
CAL	35	4
DEL	31	5
CHN	28	6
GOA	26	7
AHD	25	8
HYD	25	8
COC	15	9
CHD	0	10

Branch	Total Score	Rank
PUN	44	1
MUM	40	2
BLR	36	3
CAL	35	4
DEL	31	5
CHN	28	6
GOA	26	7
AHD	25	8
HYD	25	8
COC	15	9
CHD	0	10