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

[fashid]

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

[Anonymous]

See attached

[sunny_talwar]

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

[Anonymous · Accepted solution]

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

[sunny_talwar]

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;

[nagaiank]

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

[fashid]

Yes Sunny i am looking for the same output

[fashid]

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

[sunny_talwar]

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;

[fashid]

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