Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear ALL,
I want to select top 3 rankers from given table as below:-
Location Total Inward
Banglore 1
Gaziyabaad 5
Thane 3
Chennai 20
Delhi 25
Bhopal 16
Expected output :-
Location Total Inward
Delhi 25
Chennai 20
Bhopal 16
I want the output should return Top 3 order from normal one...in case of oracle i used rownum >= number( specified by us)....how i can achieve the same in QlikView.
Sarfaraz
Hi,
Take calculated
Dim:- =aggr(if(Rank(sum(Total_R))<=3,Location),Location)
Expre:- =sum(Total_R)
Note:- And in dimension select suppress when value is null option selected.
Regards
Anand
Dear All,
Thanks for your response...but still i am confused ...my QVW file is attached here for your reference.
Dear amit you answer is correct...but what is this rank() and Aggr() doing here ...can you please explain me in brief..
Sarfaraz
Hi,
Rank Function:
The rank function will identify the place a row has in a table based on the field you specify.
I have a current example of a call center where we rank each member in the team according to the value of the sales they make in a month.
By making the rank the primary sort field we get:
Rank Name Sales
1 Richard Jones £200,000
2 Richard Smith £180,000
3 Richard Name £150,000
This is a simple example where the rank expression is simply RANK(Sales).
For Aggr() function please see this link:
QlikView Technical Brief - AGGR.docx
Thanks,
AS
Hi Sarfaraz,
If you use Rank you get the rank value to your values for this see the example.
If You use =Rank(Total_R) it will show 1,2,3...ranking to your rows and
if you use =if(Rank(sum(Total_R))<=3,Location) this will show top 3 Location but as you use it in the dimension you need to aggregate it by Location for display Locations
=aggr(if(Rank(sum(Total_R))<=3,Location),Location)
Location | =sum(Total_R) | =Rank(Total_R) |
Delhi | 25 | 1 |
Chennai | 20 | 2 |
Bhopal | 16 | 3 |
Gaziyabaad | 5 | 4 |
Thane | 3 | 5 |
Banglore | 1 | 6 |
Regards
Anand
Hi anand,
Thanks a lot ........It is working fine as expected ....but one request to you ...can please explain me what aggr() function as i do not have the clarity about this function ...
Sarfaraz
Hi Sarfaraz,
Try it,
if(aggr(rank(count(isnull(INWD_CREATOR))), LOCATION)<=3, LOCATION)
Hi Sarfaraz ,
It is not possible to define this in two line that's why i have shared that link with you, if you really want to know what can a aggr function can do please follow the same.
Thanks,
AS
Hi,
You have to do the practice for this and with any samples you learn by testing the data. Basically when you use any Aggregation function in the dimension you have to aggregate the expression to get desire rows.
Suppose in the dimension you write expression you get nothing
=if(Rank(sum(Total_R))<=3,Total_R)
But same if you write in the expression
=if(Rank(sum(Total_R))<=3,Total_R)
And dimension is Location you get three rows because when you use it expression it get aggregated to the Location dimension
Same if you use it like
Dim:- =Aggr( if(Rank(sum(Total_R))<=3,Total_R),Location)
Here you need to aggregate it with Location in the dimension and expression is used
Expre:-=sum(Total_R)
Regards
Anand
See this