Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gireesh1216
Creator II
Creator II

Top two ranks in columns

Hello Team,

How to find top two ranks in column level.

I have a data like below:

IDCOMPSALES
1216A10
1216B20
1216C30
1217D40
1217E50
1217F60

 

Expected results:

IDRANK1RANK2
1216CB
1217FE

 

Labels (1)
  • Rank

4 Replies
zhadrakas
Specialist II
Specialist II

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
;
gireesh1216
Creator II
Creator II
Author

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

Sergey_Shuklin
Specialist
Specialist

Hello!

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

two_ranks_pic1.png

sunny_talwar

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)