Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
Alexander_Thor
Employee
Employee

As your QlikView deployment grows in size it can quickly get unwieldy unless you start imposing some sort of structure and standards to it.
In Computer Science we sometimes talk about Separation of concerns, http://en.wikipedia.org/wiki/Separation_of_concerns, although it is not 100% applicable for QlikView it does make sense to try to split the different pieces of a QlikView document apart into re-usable components.

To help you with that QlikView has the functions Include and Must_Include.

Essentially what this allows you to do is to create reusable snippets of load script that you can then re-use across your deployment. All of those connection strings to databases are prime candidates for a include statement, 1 statement that can be referenced from multiple documents and 1 place to change it when your connection string changes.

Connection strings are just one example, I've used it in the past to store the corporate graphical profile as variables that I can then include in all my documents or why not a standard calendar and date formats?

Our own Qlik Deployment Framework also heavily relies on Include statements to allow apps to be portable between different servers and to promote code re-use. You can join the QDF group here, QlikView Deployment Framework


So how do I use it?
To quote the help file,

$(Include =filename )
$(Must_Include =filename )

The include and must_include variables specify a file that contains text that should be included in the script. The entire script can thus be put in a file. This is a a user-defined variable.

The difference between include and must_include is that include will fail silently if the file is not found during script reload, while must_include will throw an error if the file is not found.

Examples:
$(Include=abc.txt);
$(Must_Include=def.txt);

We make use of dollar sign expansion to expand the contents of an external file into our load script.
The file path supports both absolute and relative paths making it ideal for portability between test and production servers.

If you ever were a fan of Inception then you could start structuring your Include statements with nested dollar sign expansions to manage where to load the Include files from,

let env = '<A absolute or relative file path, for example ..\Config\Test\>';
$(Include=$(env)\ConnectionString.txt)

This will also be my last post for the Design Blog, unless I make a guest appearance , so I hope you have enjoyed reading my posts as much as I have enjoyed writing them.

18 Comments
simondachstr
Luminary Alumni
Luminary Alumni

Using

  • Notepad++,
  • MatthewFryer 's Notepad++ language definition for QlikView,
  • .qvs files and the
  • $(include) function

is the best way to script in my opinion.

4,897 Views
Alexander_Thor
Employee
Employee

I totally agree, in addition I would recommend the InQlik tools ( inqlik/inqlik-tools · GitHub ) for Sublime which I absolutely love.

The only drawback I can see if you have a lot of temporary people writing script for you as it introduced a new tool into the toolset but it's far outweighed by the increased productivity in my opinion at least.

4,897 Views
Not applicable

Hello Alexander!

Thanks for your insight.

I'm new to Qlik Products.

I'm working so far mostly with Qlik Sense. Would InQlik tools be useful for scripting in Qlik Sense also?

Thanks and have a great weekend!

0 Likes
4,897 Views
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Thanks Alexander.  Good to see Must_Include getting a mention on the Qlik Design blog, as it made it into my blog post on 'hidden' features a while back - as there was literally no mention of it in any documentation.  This seemed crazy to me, as it is (in my opinion) more useful that it's non-must counterpart.  I've seen people caught out many times where an include file has gone missing and the QV script runs without error - but doesn't do what it was supposed to.

The blog post can be found here:

http://www.quickintelligence.co.uk/hidden-qlikview-features/

Steve

0 Likes
4,897 Views
sspe
Creator II
Creator II

I'm also quite fond of the concept of loading script files with Connection string, Dates, Variables etc., but if it's being used to much you loose some readability in your QV file.

A connection string is maybe not a dig deal, but if you have a fairly large Calendar script, a variables script and maybe other "standard" parts, then it's not always that easy to read and to know what's being loaded when it runs.

For things you use often you might know the script(s) very well but in other cases it's a bit cumbersome to have to look in the script file again and again to see what the script actually do.

That being said, I still think it's a great idea to use the (Must)Include and have standard scripts..:-).

/Steen

0 Likes
4,897 Views
datanibbler
Champion
Champion

Yep.

I totally agree with you.

I have never used the Must_Include yet as the qvs files are kept on a server path that few people have access to and I guess no one would think of deleting those - few people ever think of deleting anything in our company which is - well, that's a different issue.

I know Notepad++ too, but I think it's not in our standard_software_kit and you have to have IT install it.

Include_files are a great way to store often_used pieces of information in just one place.

I wouldn't necessarily use it for entire pieces of script_code, but the paths to all the base_files, the master_calendar and many others are in qvs files (or just plain txt, works just the same).

0 Likes
4,897 Views
Not applicable

After reading Steve Dark's blog article about 'must_include', I started using it, and it has saved me hours of grief. We have a very "Topsy-like" QV implementation - it just growed! I now have over 100 QVW's and hundreds of reports generated through nPrinting every day, and when we recently decided to move some of our servers into the cloud, I knew it was time to get systematic.

I have Include files for each different connection string (I regularly connect to about a dozen different dbs), and for many of the standard SQL pulls from my tables. HERE'S A HINT THAT I HAVEN'T SEEN ELSEWHERE: If you are going to put your SQL pull into an Include file, end your text file with a blank, not a semicolon. Then, when you use the Include file, you can have syntax such as:

$(Must_Include=C:\MyDoc\MyDb\Filename) <- note: no semicolon here either!

(Now you can add your conditions as needed for the particular file)

WHERE

     some condition, etc.

You can easily add WHERE's and GROUP BY's (even JOINs) while still using your include file.

I have some files now where the script is only two lines of code: IncludeFile for Connection String, and IncludeFile for table.

The other thing I use Include files for is parameters that change frequently. For example, we have CSR's,
Admins, and Sales people. The list of each doesn't change every day, but it changes enough that it's a pain for me to constantly update filters, bookmarks, etc. with the current list. What we do now is Admin enters all the data in a spreadsheet, and my IncludeFile reads the data, and creates variables using code like this:

USADMIN:

LOAD Ugroup,

     Member

FROM

(ooxml, embedded labels, table is [ADMIN US]);

SET vAdminUS=; // Initialize variable

SET comma=; // Initialize variable

SET esc = chr(01); // The character we will use to escape quotes

// Loop through the rows of USAdmin to build the expression string,

FOR i = 0 to NoOfRows('USADMIN')-1

  LET vAdminUS = vAdminUS & comma & $(esc) & peek('Member', i, 'USADMIN') & $(esc);

  SET comma = ','; // Next time through the loop, insert a comma

NEXT i

LET vAdminUS = replace(vAdminUS, $(esc), chr(39));

DROP TABLE USADMIN;

The output is a variable, vAdminUS that looks like 'Tom','Dick','Harry', etc. and can be used in, e.g.

WHERE

     OpTable.USER IN ($(vAdminUS))

One advantage to this is you can use the variables as filters if you use the "nPrinting" extension to Qlik.

3,921 Views
Not applicable

If you use the same connection string in 20+ files, and it's changing more than once, it's a great benefit to have them in IncludeFiles. Saved me hours during our recent transition to the cloud.

0 Likes
3,921 Views
Alexander_Thor
Employee
Employee

These are some great comments on this post.
I'm super glad for all the great examples, keep em coming!

0 Likes
3,921 Views
Not applicable

Hi all , first thanks a lot for all the posts .

is there any way to load the script from a different source than the local filesystem of the Server  ?

0 Likes
3,921 Views