Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

narband2778
New Contributor III

How to get second Highest Rank for each ID in table

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?   

Tags (1)
1 Solution

Accepted Solutions
agigliotti
Honored Contributor II

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

maybe this:

t1:

load

*

from your_table;

left join

load

ID,

max(Rank, 2)  as your_rank

resident t1 group by ID;


t2:

load

*

where your_rank_flag = 1;

load

*,

if( Rank = your_rank, 1, 0 ) as your_rank_flag

resident t1;


drop table t1;


4 Replies
agigliotti
Honored Contributor II

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

maybe this:

t1:

load

*

from your_table;

left join

load

ID,

max(Rank, 2)  as your_rank

resident t1 group by ID;


t2:

load

*

where your_rank_flag = 1;

load

*,

if( Rank = your_rank, 1, 0 ) as your_rank_flag

resident t1;


drop table t1;


ogster1974
Honored Contributor II

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

Make this your ID dimension and exclude nulls.

=IF(AGGR(Rank(SUM(Value)),ID)=2,ID,NULL())

narband2778
New Contributor III

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

Worked Perfectly.. Thanks.

omarbensalem
Esteemed Contributor

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

You don't need to alter ur script.

Just Create a simple table:

as a dimension 1: ID

as a dim 2: (and uncheck show null values)

=aggr(if( aggr(rank(sum(Value), ID),ID,Value)=2,Rank),ID,Rank)

as a measure: sum(Value)

Result:

Capture.PNG