Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
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)