Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
narband2778
Contributor II

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

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

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
Contributor II

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

Worked Perfectly.. Thanks.

Partner
Partner

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