Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Select top N in dimension

Dear Qlikview community,

I have a table like this:

YearCompanyPostingLine itemScore
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

12 Replies
amartinez35
Valued Contributor

Re: Select top N in dimension

Hi,

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

Best,

Aurélien

Not applicable

Re: Select top N in dimension

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.

stephen-a_redmo
Valued Contributor II

Re: Select top N in dimension

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

Not applicable

Re: Select top N in dimension

top N.PNG.png

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

amartinez35
Valued Contributor

Re: Select top N in dimension

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

iktrayanov
Contributor III

Re: Select top N in dimension

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

Not applicable

Re: Select top N in dimension

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.

Not applicable

Re: Select top N in dimension

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?

Not applicable

Re: Select top N in dimension

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

Community Browser