This is the second part in our Back To Basics series of posts.  This post looks at a simple way to keep track of how long a QlikView load takes and how much it is doing.

The Goal

This tip will only take a minute or two to implement, but will give you and your users an immediate visibility of vital information about your document. When it last refreshed, how long it took and how many rules of data were pulled in. The end result will look like this:

QlikView Load Stats

Setting Up

To achieve this you will need to set up some variables at the start of your code, to capture the time and to initialise the row counter:

let vLastLoadStart = now(); let vNoOfRows = 0;

You can also capture some other information here. Typically I will set a version number, like this:

let vVersionNumber = 1.03;

Other information can be captured regarding the load here also, such as the machine name and user account used to run the load. Typically I will then set various environment variables after that; such as date formats, colour values and environment variables (such as show/hide states).

At this point I will then tend to our in another tab – to start the load proper.

Keeping Count

To get a row count you will need to add the number of rows in each table to your counter. The code for this is simply:

let vNoOfRows = vNoOfRows + Alt(NoOfRows(‘TableName’), 0);

The Alt statement here ensures your counter is not obliterated if a single table doesn’t load. If you have resident loads or lookup tables in your load script think about whether you want to include these in your count – there’s no right or wrong just as long as you are aware of what it is you are taking.

The other bit of information you need to show the stats that we are after is when the load completed. I will typically put this on an End tab before an Exit Script statement (I will cover the reasons for this elsewhere). The code for this reads:

let vLastLoadEnd = now();

Displaying The Output

The output of this information is done using one of the most n useful and flexible objects QlikView has to offer: the Text Box. Simply place a text box on the front page of your app with the following code in it:

=’Last load date: ‘ & date(vLastLoadStart, ‘DD MMM YYYY hh:mm:ss’) &
‘ Last load duration: ‘ & date(vLastLoadEnd – vLastLoadStart, ‘hh:mm:ss’) &
‘ No of rows: ‘ & num(vNoOfRows, ‘#,##0’) &
‘ Version no: ‘ & vVersionNumber

Typically I will put this at the bottom left corner in a small font, with a grey colour and transparent background. Attention does not need to be drawn to this information – but it is good to be there. If you want to copy and paste a ready made text area you will find them on some of the shared QlikViews I have uploaded to QlikCommunity.

Taking This Further

This simple view of performance is often enough to keep a quick eye on what is happening with your loads. If you need tending of load performance or more granular information (eg. rows and performance of each table in a load) then you need to persist information into QVDs by inserting values into tables during the load – using the AutoGenerate function.

This is the second part in our series of Back To Basics posts, click here to view other posts in this series.