Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
Make this your ID dimension and exclude nulls.
=IF(AGGR(Rank(SUM(Value)),ID)=2,ID,NULL())
Worked Perfectly.. Thanks.
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: