Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

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

Best,

Aurélien

Help users find answers! Don't forget to mark a solution that worked for you!
Not applicable
Author

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.

stephencredmond
Luminary Alumni
Luminary Alumni

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
Author

top N.PNG.png

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

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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

Help users find answers! Don't forget to mark a solution that worked for you!
iktrayanov
Creator III
Creator III

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
Author

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
Author

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
Author

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