Straight Table Pagination

Example of Straight Table Pagination

Straight tables are one of the most commonly used objects in Qlik. I would say there are no reports without tables. Even if we try to avoid them, they are always there.

Tables don’t usually present a problem in our reports. However, when we work with big amount of data, tables don’t perform well and we need to start applying filters to make them smaller. No matter how big and dirty tables can get, users still want all of the detailed data in a table. No filters, no shortcuts. All of it.

This presents a big problem from the performance point of view. None of the objects in Qlik allow pagination. This means, that the whole table will be calculated at run time when it’s visible.

Pagination techniques allow applications to load part of the data at a time instead of all of it. When user selects a different page in the table, only the data belonging to the page is displayed and the rest is disregarded.

Example of Straight Table Pagination

Example of Straight Table Pagination

Pagination in Qlikview can’t be done out of the box. Instead we need to use different technics at design time or make changes in our script to add pagination at the table level. We could also use extension objects.

The solution I’m presenting today is done completely at design time so our script remains untouched.

We only need 3 variables. For this example I used:

  • vPaginationLines: number of lines we want to display per page.
  • vPagination: configuration variable to make the pagination work.
  • vDimensions: used to calculate total number of rows.

Variables can be created from within the script or from the Variable Overview menu. vDimensions should have the dimensions used in the table separated by “&”.  If I’m displaying Sales by Country and Department, then my vDimensions variable would look like Country&Deparment.

In the table expression, I used the following if statement:

if( rowno(Total) >= $(vPagination)$(vPaginationLines)+1 And RowNo(Total) <= $(vPagination), tableExpression))

Please feel free to download the Sample Application here.

JV

Advertisements

Tags: , , , , , ,

7 responses to “Straight Table Pagination”

  1. Naomi says :

    I’m don’t see how you limited your records displayed in the table to just those in which the expression is greater than 0 or not null. I’ve been attempting to do this in a table, and it won’t limit my results. I have Suppress Zero-Values and Suppress Missing checked off in the Presentation tab.

    • Naomi says :

      okay, I got it. Other expressions were causing it to not work. I had to change all of my other expressions to follow the same formula – they only display if the rowno is in the correct range.

  2. Business Intelligence Experience says :

    Hi Naomi, I’m glad you were able to sort this out. If you have any questions, please let me know.

    JV

    • Noelle says :

      JV, Thanks for the easy tutorial. Could you please help me understand why I am not able to suppress the remaining values from the table? I have only one expression but yet I see the records outside of the page limits appear in the table as null.
      Thanks.

  3. Antonio says :

    Hi JV, this is a great example on how to limit records in a table. It’s a shame this can not be applied as a feature in Qlikview.Do you know if they will implement it?

    Best
    Antonio

  4. Business Intelligence Experience says :

    Hi Antonio,

    I know this is a very required featured and I know Qlik knows it but I haven’t heard anything about this featured being implememented in the near future. I invite you to add your ideas to the Ideas section in the comminity: https://community.qlik.com/community/pvt/ideas

    Juan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: