Qlikview Display Table Counts Before and After Reload

About a week ago, I was working on changes for a Qlikview application when the user asked me how the record counts for a given table changed, following the most recent refresh.  This got me thinking about ways I could display the changes, which led me to creating this post.  The following details a simple way to generate a before / after snapshot of table details when a Qlikview application is refreshed (reload).

Qlikview Display Table Counts Before and After Reload – Setup

Let’s say you have the following table:

Qlikview Display Table Counts Before and After Reload

This table is a simple load from an Excel Spreadsheet.  Next, add the following code to your Load Script, in a separate tab:

Courtesy of the following post on the Qlik Community Forums:  Qlik Community Forum – Thread 210046

This snippet of code will loop through all tables in your Qlik app, then store the resulting table detail into a QVD file.  I modified the code to only pull detail for table “Stats”.

In your Edit Script window, place the code above in a separate tab called “Table Counts”, and place it after your “Main” script in the tab order.  While your at it, add another tab called “Before”, as we’ll need this further down in the setup (this will be first in the tab order, as displayed below).

Qlikview Display Table Counts Before and After Reload

If you reload your Qlik app, you will now see an additional table called “TableStats”.

Qlikview Display Table Counts Before and After Reload

Setup a Table Box to display the information, similar to the following.  This represents the table detail, following the reload (hence the table box caption “After”).

Qlikview Display Table Counts Before and After Reload

Next, add the following to the Edit Script window, within the “Before” tab.  This is where the QVD file comes into play:

Again, make sure this tab is first in the tab order.  This part of the Load Script will pull in the details from the TableStats.qvd file, which is essentially your “Before” snapshot of the table detail:

Qlikview Display Table Counts Before and After Reload

Reload your Qlikview application and you will now see a third table:

Qlikview Display Table Counts Before and After Reload

Add another Table Box object to display the contents of this table, similar to the following (I’ve intentionally made the labels for each field the same as the “After” Table Box above).  This represents the table detail, before the new content is loaded (hence the table box caption “Before”).

Qlikview Display Table Counts Before and After Reload

Reload your Qlik app a couple times to sync up the QVD file, then stack the Table Box objects, like the following:

Qlikview Display Table Counts Before and After Reload

Now, add some records to your data file, then reload the Qlik app once again (one time).  You will notice the differences in the Table Box objects, similar to the following:

Qlikview Display Table Counts Before and After Reload

In my example, the following changes are illustrated:

  • The number of records in table “Stats” increased from 478 rows to 1019 rows
  • The unique field values increased
    • Field “Month” increased from 6 to 12
    • Field “Period” increased from 6 to 15
    • Field “RawPage” increased from 130 to 160
    • Field “Views” increased from 90 to 111

 

There you have it, an easy way for users to understand how table counts and field details change from one refresh to the next.

 

 

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

 

Leave a Comment