Visokio website     Downloads     Video tutorials     KnowledgeBase  
Table View: Displaying only top 'N' values - Visokio Forums
Table View: Displaying only top 'N' values
  • tagarw August 11, 2014 2:55AM
    Hi All,
    I have one Table View in an Omniscope tab in which I am selecting certain rows from source DB say 100. Then I am creating certain filters on the Table View data which reduces the data to 50. And then a Sort.

    After sort I want to display only top 5 records in final view of table. I am not able to find anything to do that. Can someone please help on limiting the records to 5.

    Please let me know if you need more information.

    Thanks.
  • 5 Comments
  •     tjbate August 11, 2014 3:38AM
    Jagran

    Section 4 of this page shows how to construct Top/Bottom N classifications which can be combined with Named Queries or other filter settings to display only the Top/Bottom N records based on a specific field:

    http://www.visokio.com/kb/useful-formulae

    There are a lot of other discussions of this type of filtering categorised under the tag 'Pareto'

    http://forums.visokio.com/discussions/tagged/Pareto
  • tagarw August 11, 2014 8:14AM
    Thank you for replying back. I have one limitation here.

    In my data explorer I have a large table of 50,000 records. In a tab I am creating 10 table views each of them have some data (with filter using named query) from that main table.

    Now If I am using rank function for top 10(As in the example) in any table view of tab. Ranking is based on the main table not the table view in the tab(Ranking is based on the 50,000 values not the data which is in table view). So it is difficult to select top 10.

    Cannot we have ranking for table view inside tab. Independent of main table.
  •     tjbate August 11, 2014 2:10PM
    Jagran - If you want to calculate sub-rankings for defined subsets of the data set, you must add the Subset Function syntax to your RANK function. The Subset function syntax is described on this page which includes an example of using a Subset 'clause' with the RANK function:

    http://www.visokio.com/kb/subset-functions

    Broadly, an Omniscope SUBSET clause has the following structure:

    SUBSETN([FieldtoConsider1],[FieldtoConsider2],...
    'DefinedValue1', 'DefinedValue2', ...
    Operator1, Operator2)...

    where 'DefinedValueN' can be a Variable, and where the same Field name is repeated in the case that the defined subset contains all values in a given [FieldtoConsiderN]. Operators must be = for Text/Categories, but can be =, !=, GTE, LTE for Numeric values and Date/Times.

    The sample RANKING function file on this page also shows examples of using RANK with SUBSET 'clauses':

    http://forums.visokio.com/discussion/2479/formulae-rank-syntax-ranking-on-aggregated-values-etc
  • tagarw August 14, 2014 3:08AM
    Thanks tjbate. Is it possible to use sub query in subset function?? Because data in view is coming from sub query. and from that data I need top10.
  •     tjbate August 14, 2014 5:25AM
    Yes, you can use Variables as defined values within SUBSET clauses, and also classify records as Top-10 or not in a Formula Field and use 'Top-10' as one of the values defining the subset to display:

    ....Subset2( [FieldtoConsider1],[Top-10],{VariableValuefromField1},'Top-10',=,=)

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership