In this article by Andrew Dove and Roger Stone, author of the book QlikView Unlocked, we will cover the following key topics:

• A few coding tips
• The surprising data sources
• Include files
• Change logs

(For more resources related to this topic, see here.)

A few coding tips

There are many ways to improve things in QlikView. Some are techniques and others are simply useful things to know or do. Here are a few of our favourite ones.

Keep the coding style constant

There’s actually more to this than just being a tidy developer. So, always code your function names in the same way—it doesn’t matter which style you use (unless you have installation standards that require a particular style). For example, you could use MonthStart(), monthstart(), or MONTHSTART(). They’re all equally valid, but for consistency, choose one and stick to it.

Use MUST_INCLUDE rather than INCLUDE

This feature wasn’t documented at all until quite a late service release of v11.2; however, it’s very useful. If you use INCLUDE and the file you’re trying to include can’t be found, QlikView will silently ignore it. The consequences of this are unpredictable, ranging from strange behaviour to an outright script failure. If you use MUST_INCLUDE, QlikView will complain that the included file is missing, and you can fix the problem before it causes other issues. Actually, it seems strange that INCLUDE doesn’t do this, but Qlik must have its reasons. Nevertheless, always use MUST_INCLUDE to save yourself some time and effort.

Put version numbers in your code

QlikView doesn’t have a versioning system as such, and we have yet to see one that works effectively with QlikView. So, this requires some effort on the part of the developer. Devise a versioning system and always place the version number in a variable that is displayed somewhere in the application. Updating this number every time you make a change doesn’t matter, but ensure that it’s updated for every release to the user and ties in with your own release logs.

Do stringing in the script and not in screen objects

We would have put this in anyway, but its place in the article was assured by a recent experience on a user site. They wanted four lines of address and a postcode strung together in a single field, with each part separated by a comma and a space. However, any field could contain nulls; so, to avoid addresses such as ‘,,,,’ or ‘, Somewhere ,,,’, there had be a check for null in every field as the fields were strung together. The table only contained about 350 rows, but it took 56 seconds to refresh on screen when the work was done in an expression in a straight table. Moving the expression to the script and presenting just the resulting single field on screen took only 0.14 seconds. (That’s right; it’s about a seventh of a second). Plus, it didn’t adversely affect script performance. We can’t think of a better example of improving screen performance.

The surprising data sources

QlikView will read database tables, spreadsheets, XML files, and text files, but did you know that it can also take data from a web page? If you need some standard data from the Internet, there’s no need to create your own version. Just grab it from a web page! How about ISO Country Codes? Here’s an example.

Open the script and click on Web files… below Data from Filesto the right of the bottom section of the screen. This will open the File Wizard: Source dialogue, as in the following screenshot.

Enter the URL where the table of data resides:

Then, click on Next and in this case, select @2 under Tables, as shown in the following screenshot:

Click on Finish and your script will look something similar to this:

LOAD F1,
     Country,
     A2,
     A3,
     Number
FROM
[http://www.airlineupdate.com/content_public/codes/misc_codes/icao
_nat.htm]
(html, codepage is 1252, embedded labels, table is @2);

Now, you’ve got a great lookup table in about 30 seconds; it will take another few seconds to clean it up for your own purposes. One small caveat though—web pages can change address, content, and structure, so it’s worth putting in some validation around this if you think there could be any volatility.

Include files

We have already said that you should use MUST_INCLUDE rather than INCLUDE, but we’re always surprised that many developers never use include files at all. If the same code needs to be used in more than one place, it really should be in an include file.

Suppose that you have several documents that use C:QlikFilesFinanceBudgets.xlsx and that the folder name is hard coded in all of them. As soon as the file is moved to another location, you will have several modifications to make, and it’s easy to miss changing a document because you may not even realise it uses the file.

The solution is simple, very effective, and guaranteed to save you many reload failures.

Instead of coding the full folder name, create something similar to this:

LET vBudgetFolder='C:QlikFilesFinance';

Put the line into an include file, for instance, FolderNames.inc.

Then, code this into each script as follows:

$(MUST_INCLUDE=FolderNames.inc) 

Finally, when you want to refer to your Budgets.xlsx spreadsheet, code this:

$(vBudgetFolder)Budgets.xlsx 

Now, if the folder path has to change, you only need to change one line of code in the include file, and everything will work fine as long as you implement include files in all your documents.

Note that this works just as well for folders containing QVD files and so on. You can also use this technique to include LOAD from QVDs or spreadsheets because you should always aim to have just one version of the truth.

Change logs

Unfortunately, one of the things QlikView is not great at is version control. It can be really hard to see what has been done between versions of a document, and using the -prj folder feature can be extremely tedious and not necessarily helpful. So, this means that you, as the developer, need to maintain some discipline over version control.

To do this, ensure that you have an area of comments that looks something similar to this right at the top of your script:

// Demo.qvw
//
// Roger Stone - One QV Ltd - 04-Jul-2015
//
// PURPOSE
// Sample code for QlikView Unlocked - Chapter 6
//
// CHANGE LOG
// Initial version 0.1
// - Pull in ISO table from Internet and local Excel data
//
// Version 0.2
// Remove unused fields and rename incoming ISO table fields to

// match local spreadsheet
//

Ensure that you update this every time you make a change. You could make this even more helpful by explaining why the change was made and not just what change was made. You should also comment the expressions in charts when they are changed.

Summary

In this article, we covered few coding tips, the surprising data sources, include files, and change logs.

Resources for Article:

---

Further resources on this subject:

• Qlik Sense’s Vision [Article]
• Securing QlikView Documents [Article]
• Common QlikView script errors [Article]

---