topic How to get second Highest Rank for each ID in table in QlikView

How to get second Highest Rank for each ID in table

narband2778 — Tue, 06 Feb 2018 10:37:01 GMT

Hello everyone,

I have Table:

ID Value Rank

1 10 1

1 20 2

1 30 3

2 25 1

2 35 2

2 42 3

2 45 4

Generally we know, for ID= 1 we will get second highest rank is 2 and for ID=2 second highest rank is 3.

desired Table should be like this:

ID Value Rank

1 20 2

2 42 3

Can someone help me with this?

Re: How to get second Highest Rank for each ID in table

YoussefBelloum — Tue, 06 Feb 2018 10:40:08 GMT

Hi,

for ID=2 why the second highest rank is 3 ?

Re: How to get second Highest Rank for each ID in table

narband2778 — Tue, 06 Feb 2018 10:43:27 GMT

Hi,

For ID = 2 there are 4 records and the highest Rank is 4. So, I want second Highest rank i.e. Rank = 3.

Re: How to get second Highest Rank for each ID in table

avinashelite — Tue, 06 Feb 2018 10:51:20 GMT

could you explain the desired output once ???

For ID 2 its should the value 35 , 2 highest rite

Re: How to get second Highest Rank for each ID in table

narband2778 — Tue, 06 Feb 2018 10:54:59 GMT

Hi,

I mean for ID =1 Maximum Rank = 3 but I need second maximum Rank which is 2 for ID=1.

Same for ID = 2 Maximum Rank = 4 but second maximum rank = 3.

i hope this is clear.

Re: How to get second Highest Rank for each ID in table

avinashelite — Tue, 06 Feb 2018 11:13:42 GMT

Got you try this script

Test:

LOAD * INLINE [

ID,Value,Rank

1,10,1

1,20,2

1,30,3

2,25,1

2,35,2

2,42,3

2,45,4

];

NoConcatenate

Max_R:

LOAD

ID&'-'& max(Rank)as Key_ID

Resident

Test

Group by

;

Second_MAX:

LOAD

ID&'-'&max(Rank) as Second_Max_Key

//max(Rank) as S_R

Resident

Test

Where not Exists(Key_ID,ID&'-'&Rank)

Group by

ID;

Drop Table Max_R;

NoConcatenate

Result:

LOAD

ID,

Value,

Rank

Resident

Test

Where

Exists(Second_Max_Key,ID&'-'&Rank);

Drop Table Second_MAX,Test;

Re: How to get second Highest Rank for each ID in table

narband2778 — Tue, 06 Feb 2018 12:00:22 GMT

Thanks Avinash

Re: How to get second Highest Rank for each ID in table

swuehl — Tue, 06 Feb 2018 12:43:38 GMT

Note that Max() can take an optional second argument to retrieve the n-th highest value.

So you can shorten the script a llttle bit:

Test:
LOAD *, ID & '-'&Rank as Key_ID
INLINE [
ID,Value,Rank
1,10,1
1,20,2
1,30,3
2,25,1
2,35,2
2,42,3
2,45,4
];

INNER JOIN
LOAD
ID&'-'& max(Rank,2) as Key_ID
Resident
Test
Group by
ID
;

DROP FIELD Key_ID;

Re: How to get second Highest Rank for each ID in table

avinashelite — Tue, 06 Feb 2018 13:05:38 GMT

Yes , but sometime it won't give the accurate result in the script