Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator 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?   

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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;


View solution in original post

4 Replies
Highlighted
Partner
Partner

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;


View solution in original post

Highlighted
Master II
Master II

Make this your ID dimension and exclude nulls.

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

Highlighted
Creator II
Creator II

Worked Perfectly.. Thanks.

Highlighted
Partner
Partner

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