12 Replies Latest reply: Mar 26, 2014 1:47 PM by Stephen Redmond RSS

    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