topic Re: Select top N in dimension in QlikView

Select top N in dimension

Wed, 26 Mar 2014 16:00:50 GMT

Dear Qlikview community,

I have a table like this:

Year	Company	Posting	Line item	Score
				4
				4
				4
				3
				3
				3

The table contains million of lines. The goal is to only show the top N records, with the table sorted on score descending. Please note that the score column contains not only distinct values. Say that 500 records have the highest score of 5 and i want to show top 100, then it should show random 100 from those 500.

i would be able to have this result using the following query:

select top 100 * from table order by Score descending. However, i would preferably not create another table for this table chart and limit the results using dimension expressions.

I have tried solutions like this one: Qlikview Bitz and Pieces: Top N in Qlikview Straight/Pivot Tables and Charts

using a combination of aggr( ), rank() and sum()

and using the dimention limits to show first 100 values for the score dimention, but i never to shows me only 100 results.

Can anybody point me in the correct direction?

Thanks in advance

Re: Select top N in dimension

Aurelien_Martinez — Wed, 26 Mar 2014 16:06:42 GMT

Hi,

I think you can use Dimensions Limite (in Chart properties).

Best,

Aurélien

Re: Select top N in dimension

Wed, 26 Mar 2014 16:21:14 GMT

I have tried using dimension limits... i select both largest or first 10 values, but i does not show top 10 records. I think this option would work i have had scores from e.g. 1 to 100 and no duplicate scores exist.

Re: Select top N in dimension

stephencredmond — Wed, 26 Mar 2014 16:22:57 GMT

Hi,

Do you want to post an example?

Just to be sure, you are using a Straight Table, not a Table Box? You can't limit a Table Box.

Usually the dimension limitations option will work fine for a Straight Table (see attached).

Regards,

Stephen

Re: Select top N in dimension

Wed, 26 Mar 2014 16:29:12 GMT

As you can see, it does not limit the records to the top/ first 10

Re: Select top N in dimension

Aurelien_Martinez — Wed, 26 Mar 2014 16:41:38 GMT

It's because you have a 1 on all your lines. You can use demension limits on the field DTT_GL_Document_Number .

Re: Select top N in dimension

iktrayanov — Wed, 26 Mar 2014 16:45:03 GMT

Dimension limits should work but you need to make sure you set your sort options correctly. If you only want to order by Score and everything else to be random you need to remove the sort from all other fields. Please see the example.

Regards,

Ivan

Re: Select top N in dimension

Wed, 26 Mar 2014 16:48:56 GMT

Hi Stijn,

the problem is that the DTT_Total_score field has the same value for more than 100 items, if you put the limit will work in another field.

best regards.

Re: Select top N in dimension

Wed, 26 Mar 2014 16:51:56 GMT

Hi guys,

Thanks for the replies. I more or less understood already that the problem was the there are no distinct values in the score column. However, none of my columns have distinct values.. only the combination of year, company code, document and line item makes the row unique. How can i include this?

Re: Select top N in dimension

Wed, 26 Mar 2014 16:53:13 GMT

All i actually really need is exactly the result of this query appearing in the table:

select top 100 * from table order by Score descending

Re: Select top N in dimension

stephencredmond — Wed, 26 Mar 2014 17:00:30 GMT

Hi,

Sort by value descending and disable interactive sort.

On the Presentation tab, select "Max Number (1-100)" and specify 100 as the value.

Regards,

Stephen

Re: Select top N in dimension

Wed, 26 Mar 2014 17:07:02 GMT

Finally, thankkkss ! It works!

Can i have a side-question ?

suppose i want to create this table in the script, from a table that is already loaded from qvd, What would be the syntax for it? since i create it from a already loaded table i guess i need to use load resident statement, is there anything equivalent to the sql syntax, like:

table:

load top 100 *

resident loaded_table

order by Score descending

Re: Select top N in dimension

stephencredmond — Wed, 26 Mar 2014 17:47:27 GMT

Use FIRST:

First 100

Load *

Resident MyTable

Order by x, y;

Regards,

Stephen