How To View QlikView Load Performance

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.

By |2013-11-26T22:23:37+00:00October 3rd, 2013|Back To Basics, Load Script, QlikView Tutorial|10 Comments

About the Author:

Steve is owner and principal consultant at Quick Intelligence. He is a Qlik Luminary, Qlik Community MVP and Technical Editor of a number of QlikView Books.

10 Comments

  1. Lucian Cotea October 4, 2013 at 6:29 am - Reply

    I want to share what other info I find useful to display:

    – ComputerName( ) and OSuser( ) when Section Access is used
    – max(ALL Date) to see if we have the latest data or maybe there’s transactions in the future.

    • Steve Dark October 4, 2013 at 10:29 am - Reply

      Hi Lucian – yes, all useful extra information. Persisting the Computer Name and OS User to variables during the load script will also tell you whether Server or a user updated the document last. As well as the Max date from the data using the file system to get the date time stamp off of the main QVD’s can be useful – as a presentation document could be refreshed at a point but if the underlying data hasn’t changed the date would be misleading. Thanks for your comment!

  2. vegar lie arntsen October 5, 2013 at 5:58 am - Reply

    If you append each every result to a single log file you can trac performance over time. Maybe compare the kpi generated rows/time . If you are using temp tables during the script the distinguish between all rows and datamodel rows.

    • Steve Dark October 6, 2013 at 1:55 pm - Reply

      Hi Vegar – you have jumped ahead on a potential future post there! I have an include file I use quite frequently that logs stats to QVD and then a simple front end document to view performance over time.

  3. Rob Wunderlich October 25, 2013 at 5:27 am - Reply

    Hi Steve,

    I prefer to use the ReloadTime() function to calculate script duration:
    ReloadTime()– vLastLoadStart
    Then you don’t have to worry about getting the vLastLoadEnd variable set.

    I use the DocLog Analyzer tool (http://robwunderlich.com/downloads/) to analyze the duration of individual statements in a scriipt.

    • Steve Dark October 25, 2013 at 6:14 am - Reply

      Hi Rob,

      Your document log analyzer tool is indeed excellent, and has helped me out on a number of occasions. It is particularly good in that the load script does not need to be amended to make it work and I can simply ask a client to email me their last log file and I can start drilling into problems.

      I still set my own start/end variables though – to give me control over what I am recording and measuring.

      Many thanks for all the great apps and components you put together and share with the community.

  4. Bhavik Mandaliya November 25, 2013 at 2:37 pm - Reply

    Hi Steve

    I want no of rows in a text box when a chart opens or restored.
    On the restore of a chart, it should give me a record count for that chart in the text box.

    Many Thanks !
    Bhavik Mandaliya

    • Steve Dark November 25, 2013 at 11:18 pm - Reply

      There is no way that I am aware of to get the number of rows in a chart. If you know your dimensions though you should be able to calculate it. Ideally you will have a counter on each row (created during the load with the syntax 1 as RowCounter,) and you can simply do =Sum(RowCounter) in your text box.

      It is not easy to pick up maximize and minimize actions on charts, so the best way to deal with showing and hiding charts (and changing your text boxes at the same time) is to use a Show/Hide Condition on the charts and set the variables that control these using actions on buttons. My blog post on Buttons covers how to do this: .

  5. Mark November 26, 2013 at 9:44 pm - Reply

    Where exactly do you put the line

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

    vNoOfRows is always zero for me…when I paste this line in to my script it displays the vNoOfRows after the = sign in red which makes me think it doesn’t see it as a variable.

    What should table name be….is it the name of the table after the FROM part of the SELECT statement and should it be in Quotes?

    Does this line only work when a LOAD statement is used?

    • Steve Dark November 26, 2013 at 10:00 pm - Reply

      Hi Mark,

      You need to initialise the variable at the start of the script, let vNoOfRows = 0; . The TableName is the name you give the table before the load statement, eg.

      MyTableName:
      LOAD
      *
      FROM….;

      If you can find my Data Profiler on QlikCommunity (there is a link from another blog post on here) you will find a working example of the code. Hope that helps!

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.