topic Re: TOP 5 in the Table in QlikView

TOP 5 in the Table

Anonymous — Wed, 27 Aug 2014 14:46:58 GMT

Hi Friends,

Need your help here. I have 3 columns (Party Name, Ratings, Amt). I have to find the Top 5 Party Name on the basis of Amt.

The challenge here for me is that there are multiple Ratings for the Party Name(so there are multiple lines and the corresponding Amt) , but i need to show only the highest rating for the party in the table, but the sum of Amount would be the Sum of the Amt not only that particular Rating.

I have attached the Data in the excel and even attached the OUTPUT in the same Sheet.

Please let me know if this is not understandable..

Require your help...

Thanks

Ankit Modi

Re: TOP 5 in the Table

datanibbler — Wed, 27 Aug 2014 15:00:30 GMT

Hi Ankit,

so I understand you need the Amt twice - once the individual amound (by which the records are to be filtered and by which you want to single out the top5) and once the total rgd. one particular Party_Name - correct?

Well, I would recommend doing this in two steps although you could possily do it in one - it just makes it a bit easier to understand for others who might end up having to work with your code:

1) Generate a field for the total_amt in the table (aggregate the table in a RESIDENT LOAD using the sum() function

- don't forget the GROUP BY clause - actually, for this aggregated RESIDENT LOAD you need only two fields,

the Party_Name and the individual_amount (which you sum up)

2) Load RESIDENT from the primary table, again aggregating and using the max() function - you will need five lines

with the max() function, each with a different parameter - max(amt, 1), max(amt, 2) etc.

3) To this table (which should have only 5 lines), you join the total_amt, using the Party_Name as the Join_field

It might be done otherwise and you could possibly do it all in fewer steps, but this would be my approach.

HTH

Best regards,

DataNibbler

Re: TOP 5 in the Table

MK_QSL — Wed, 27 Aug 2014 15:05:50 GMT

Create a Straight Table

Dimension = Calculated Dimension

=IF(Aggr(Rank(SUM(Amt),4),Party)<=5, Party)

Tick Suppress When Value is Null

Expression1

SUM(Amt)

Expression2

FirstSortedValue(Ratings, -Amt)

Re: TOP 5 in the Table

Anonymous — Wed, 27 Aug 2014 15:07:10 GMT

Hi DataNibbler,

Thanks for reply, but i need the Amt only once not Twice as you can see the output in my earlier attached sheet,

Re: TOP 5 in the Table

Wed, 27 Aug 2014 15:07:45 GMT

Hi Ankit,

PFA,

Re: TOP 5 in the Table

Anonymous — Wed, 27 Aug 2014 15:21:48 GMT

Thanks Manish..

Please can you explain what 4 means in the below expression.

IF(Aggr(Rank(SUM(Amt),4),Party)<=5, Party)

Re: TOP 5 in the Table

Anonymous — Wed, 27 Aug 2014 15:22:34 GMT

HI Mukesh, Thanks for the reply just wanted to know if i dnt use Mid function will it work? As i dont need to exculde O

Re: TOP 5 in the Table

Wed, 27 Aug 2014 15:33:51 GMT

Hi Ankit,

you can, but you need to do some changes in backend script. simply in above qvw --> on resident table change

'O'&max(Ratings) as Ratings,

Re: TOP 5 in the Table

MK_QSL — Wed, 27 Aug 2014 15:42:52 GMT

IF(Aggr(Rank(SUM(Amt),4),Party)<=5, Party)

It will load only those party, whose SUM(Amt) is Ranked as TOP 5..

It will be simplified as below

SUM(Amt) = SUM of Amount

Rank(SUM(Amt),4) = Rank of SUM of Amount with same SUM(Amount) will be given different Rank...

Aggr(Rank(SUM(Amt),4),Party) = Same as above but Aggregated by Party... This is same as Group By of SQL

Now Checking with IF condition, and displaying only TOP 5...

Re: TOP 5 in the Table

Anonymous — Wed, 27 Aug 2014 15:45:05 GMT

Hi Mukesh,

The output is not matching with your solution.. You can chk in my Output file for Party name ZINC INL the rating is O7 whereas you are getting O9.

Re: TOP 5 in the Table

MK_QSL — Wed, 27 Aug 2014 15:48:54 GMT

Hi Ankit,

Have your tried my solution?

Re: TOP 5 in the Table

Anonymous — Wed, 27 Aug 2014 15:57:40 GMT

HI Manish,

ya when i am using in my Actual application its not working.

Re: Re: TOP 5 in the Table

MK_QSL — Wed, 27 Aug 2014 15:59:07 GMT

Check enclosed file...

Re: Re: TOP 5 in the Table

Wed, 27 Aug 2014 17:53:15 GMT

Hi Ankit,

PFA

Re: TOP 5 in the Table

Anonymous — Wed, 27 Aug 2014 18:23:30 GMT

Thanks Manish

Re: TOP 5 in the Table

Anonymous — Wed, 27 Aug 2014 18:24:23 GMT

Thanks Mukesh.. One last Question how you restricted Top 5 in your application?

Re: TOP 5 in the Table

MK_QSL — Thu, 28 Aug 2014 08:11:47 GMT

If you get your solution... kindly close the thread by selecting the correct/helpful answer

Re: TOP 5 in the Table

Thu, 28 Aug 2014 10:14:07 GMT

Hi,

Table Properties --> Presentation --> Max Number --> only top 5(Sorting on Amount in Sort tab also)