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:
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
For i =0 to NoOfTables()-1
LET vTableName = TableName($(i));
IF TableName($(i)) = 'Stats' Then
For j = 1 To NoOfFields('$(vTableName)')
LET vFieldName = FieldName($(j),'$(vTableName)');
'$(vTableName)' as TableName,
NoOfRows('$(vTableName)') as RowCount,
NoOfFields('$(vTableName)') as FieldCount,
'$(vFieldName)' as FieldName,
FieldValueCount('$(vFieldName)') as FieldValueCount
STORE TableStats INTO TableStats.qvd (qvd);
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).
If you reload your Qlik app, you will now see an additional table called “TableStats”.
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”).
Next, add the following to the Edit Script window, within the “Before” tab. This is where the QVD file comes into play:
LOAD TableName as TableNameBefore,
RowCount as RowCountBefore,
FieldCount as FieldCountBefore,
FieldName as FieldNameBefore,
FieldValueCount as FieldValueCountBefore
[C:\BuffaloBI - Post Content\Qlikview\Table Counts\TableStats.qvd ]
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:
Reload your Qlikview application and you will now see a third table:
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”).
Reload your Qlik app a couple times to sync up the QVD file, then stack the Table Box objects, like the following:
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:
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.