topic Re: Top two ranks in columns in QlikView

Top two ranks in columns

gireesh1216 — Sat, 16 Nov 2024 03:24:08 GMT

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

Re: Top two ranks in columns

zhadrakas — Tue, 04 Jun 2019 10:55:59 GMT

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 ;

Re: Top two ranks in columns

gireesh1216 — Tue, 04 Jun 2019 11:09:19 GMT

Thanks...But I am looking for front end calculation..Is there any way to calculate?

Re: Top two ranks in columns

Sergey_Shuklin — Tue, 04 Jun 2019 11:35:21 GMT

Hello!

Yes, you can do this in front-end. Use aggr() function and the second parameter of max() function.

Re: Top two ranks in columns

sunny_talwar — Tue, 04 Jun 2019 11:40:40 GMT

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)