Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

The $(Include) which you $(Must_Include) into your toolkit

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
Luminary
Luminary

Using

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

is the best way to script in my opinion.

737 Views
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.

737 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
737 Views
MVP & Luminary
MVP & Luminary

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
737 Views
sspe_dgs_com
Contributor 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
737 Views
datanibbler
Esteemed Contributor

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
737 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.

737 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
737 Views
Employee
Employee

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

0 Likes
737 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
737 Views
MVP & Luminary
MVP & Luminary

You can use a full UNC path (i.e. \\myserver\c$\myfile\).  Mapped drives can work, but often they exist for the developer but the service user that the Server runs as is not aware of them - so I would avoid these.

In order to use a script that is on a web server you would have to get a bit more clever - perhaps loading the file into a table then using a STORE to write the include file - but this is probably not a good idea.

Steve

737 Views
Not applicable

Hi Steve , thanks a lot for your help!

737 Views
Partner
Partner

Hi

what If in case we have multiples script files in text doc to be loaded in to QlikSense, How is that possible?

Ref to question I posted:Loading Script from multiple txt files to multiple tabs

Anand

0 Likes
737 Views
Partner
Partner

How about multiple includes where you need it?
The include is just a simple text inclusion of the file content.

0 Likes
737 Views
Partner
Partner

Hi Anders

Thanks for the prompt Response

So you mean add include for every tab which will have multiple includes in the App script- not sure if that works, will try

So Qualifying/data massaging of  the script or modifying should be done only in the script (txt file) or post loading in to the Qlik, will I be able to do it like an incremental load?

The main objective is that I want to maintain sql script in the text doc and do modifications on that script post loading it to Qlik.

Thanks

Anand

0 Likes
737 Views
sspe_dgs_com
Contributor II

As Anders mention, you can just add the include statement everywhere you want to load a script part from a file. If you have all your SQL selects in seperate txt files, you can just load each of these files with the Include statement just like if you have written the SQL statement directly in your script.

That being said, then I'd be a little cautious in making extensive use of includes. There are no doubt that it will work, but the readability of the script in Qlikview will be very poor if to much happens in seperate script files. When you need to debug your script or later on make modifications, it's quite cumbersome to open too many txt files one by one to see what they are doing. I have a few older app's that have been made in that way (by some former consultants) and it's a real pain to figure out the business logic in these app's because I'll have to open a lot of txt files.

I agree it can be tempting to have a lot of scripts with your SQL statements that you are using in many different apps, because you then only have to modify it in one place. What I often find though, is that it's not that often you have that many apps that are so alike that this pays of in the end. Quite often you have to make minor changes any way and/or after you have modfied the txt file, you'll have to edit something in your app anyway.

Where I do find Include files useful, is to load some "standard" files like e.g. Date dimensions, Color code files, Variable files etc. Other than that, I'd in most cases prefer to have the SQL statements etc. directly in the script.

/Steen

0 Likes
737 Views
Partner
Partner

Oh, Let me give it a try and will reply back. Thanks or all the help Anders & Steen

0 Likes
737 Views
Partner
Partner

That helped Steen, Thanks a ton

0 Likes
737 Views