Select top N in dimension

[#1]

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

[stephencredmond]

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

[#3]

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

[stephencredmond]

Use FIRST:

First 100

Load *

Resident MyTable

Order by x, y;

Regards,

Stephen