topic Re: Regarding top 3 in QlikView

Regarding top 3

sarfaraz_sheikh — Tue, 23 Dec 2014 06:21:53 GMT

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

Re: Regarding top 3

Anonymous — Tue, 23 Dec 2014 06:28:33 GMT

Hi Use like this : you will get row no.

load *,

RowNo() as S_No

Resident table_1

ORDER BY [Total Inward] ;

Re: Regarding top 3

its_anandrjs — Tue, 23 Dec 2014 06:36:22 GMT

Let me know this is table fields or it is any chart.

Regards

Anand

Re: Regarding top 3

anbu1984 — Tue, 23 Dec 2014 06:39:24 GMT

Temp:

Load * Inline [

Location,TotalInward

Banglore,1

Gaziyabaad,5

Thane,3

Chennai,20

Delhi,25

Bhopal,16 ];

NoConcatenate

Final:

First 3 Load * Resident Temp Order by TotalInward desc;

Drop Table Temp;

Re: Regarding top 3

Anonymous — Tue, 23 Dec 2014 06:42:53 GMT

can use like this

INTAB:

LOAD * Inline

[

ID,Type,Sal

1,1,12

1,3,12

2,1,11

2,2,15

3,3,22

4,1,10

4,2,10

4,3,20] ;

SECOND:

load ID As ID_R,Type AS Type_R,Sal AS Date_R

Resident INTAB where RecNo()<=3 order by Sal desc;

Re: Regarding top 3

sarfaraz_sheikh — Tue, 23 Dec 2014 06:43:09 GMT

Hi anand,

It is chart leval calculation....i used one fiels 'Inwd_Creator' in expression and 'location' as dimention.

And i used count(Inwd_Creator) in Expression for Total Inward ...and i am getting the result same as above.However i want to display Top 3 who has fall in top 3 category..

How i can achieve ....please help

Sarfaraz

Re: Regarding top 3

amit_saini — Tue, 23 Dec 2014 06:44:08 GMT

Hi,

Like this????

Thanks,
AS

Re: Regarding top 3

sujeetsingh — Tue, 23 Dec 2014 06:45:07 GMT

Order your data according to top Inward descending.

use rowno() function

Now in UI just use Dimension limit or calculated dimension.

Re: Regarding top 3

amit_saini — Tue, 23 Dec 2014 06:46:34 GMT

Correct one

Re: Regarding top 3

alkesh_sharma — Tue, 23 Dec 2014 06:54:27 GMT

Hyi,

Please find the attached qvw for your reference.

Re: Regarding top 3

its_anandrjs — Tue, 23 Dec 2014 06:55:23 GMT

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

Re: Regarding top 3

sarfaraz_sheikh — Tue, 23 Dec 2014 07:05:10 GMT

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

Re: Regarding top 3

amit_saini — Tue, 23 Dec 2014 07:13:06 GMT

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

Re: Regarding top 3

its_anandrjs — Tue, 23 Dec 2014 07:14:11 GMT

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

Re: Regarding top 3

sarfaraz_sheikh — Tue, 23 Dec 2014 07:25:49 GMT

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

Re: Regarding top 3

tyagishaila — Tue, 23 Dec 2014 07:27:45 GMT

Hi Sarfaraz,

Try it,

if(aggr(rank(count(isnull(INWD_CREATOR))), LOCATION)<=3, LOCATION)

Re: Regarding top 3

amit_saini — Tue, 23 Dec 2014 07:31:40 GMT

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

Re: Regarding top 3

its_anandrjs — Tue, 23 Dec 2014 07:45:53 GMT

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

Re: Regarding top 3

ecolomer — Tue, 23 Dec 2014 07:47:52 GMT

See this