Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Team,
How to find top two ranks in column level.
I have a data like below:
ID | COMP | SALES |
1216 | A | 10 |
1216 | B | 20 |
1216 | C | 30 |
1217 | D | 40 |
1217 | E | 50 |
1217 | F | 60 |
Expected results:
ID | RANK1 | RANK2 |
1216 | C | B |
1217 | F | E |
hey,
this way you can do it in script:
INPUT:
LOAD * INLINE [
ID, COMP, SALES
1216, A, 10
1216, B, 20
1216, C, 30
1217, D, 40
1217, E, 50
1217, F, 60
];
FINAL:
Load *,
Autonumber(recno() , ID) as RANK
Resident INPUT
order by ID, SALES desc
;
drop table INPUT;
//pivot rank1 columns
RANK:
Load ID,
COMP as RANK1
Resident FINAL
WHERE RANK=1
;
//pivot rank2 columns
Left join (RANK)
Load ID,
COMP as RANK2
Resident FINAL
WHERE RANK=2
;
Thanks...But I am looking for front end calculation..Is there any way to calculate?
Hello!
Yes, you can do this in front-end. Use aggr() function and the second parameter of max() function.
You can also try this
FirstSortedValue(COMP, -SALES)
and
FirstSortedValue(COMP, -SALES, 2)
or this
FirstSortedValue(COMP, -Aggr(Sum(SALES), ID, COMP))
and
FirstSortedValue(COMP, -Aggr(Sum(SALES), ID, COMP), 2)