When developing QlikView apps it is a good idea to develop standards that you adopt across all of your documents. A good way of ensuring these standards are followed is to use Include files in your load scripts.
QlikView Include files are snippets of code that are stored as text files outside of the QlikView app. Use of includes across multiple apps allows for effective code reuse.
Perhaps the most common use for include files is to store database connection details. By this simple route, when database credentials require changing they only need to be updated in the include file and all documents that use that include file would then automatically use the new credentials. An other potential advantage for this approach is to toggle easily between production and test database environments by having different include files on different servers.
Database connections however are just one example of the sort of code you can place in an include file. Things that I have used them for in the past have included setting default variable values (such as storing RGB values) and calling a routine to log the performance of load scripts.
The point is that anything that can go into a load script can be placed into an include file.
The thing to watch though is, as with using dollar sign expanded variables, that the script executes as if all code is contained in one script. So if, for example, you commence a comment block within your include file (with a /* ), but neglect to close it, all subsequent code in your load script will effectively be commented out. As QlikView is not aware of the contents of your include file as the script is being edited; the syntax highlighting will not show the subsequent code as commented. This can be quite confusing – so be careful of this when using include files.
[Please note: see this subsequent blog post on resolving this]
Syntax highlighting is worth mentioning here, as due to your include file code being stored in a text file; QlikView is not able to do syntax highlighting for you. One way around this is to edit the code in QlikView first and then copy and paste this code into an include file afterwards. Alternatively, Matt Fryer has provided a syntax highlighting definition for Notepad++, which he has made freely available for download here.
So, after discussing the why’s of QlikView include files, here’s the how. You simply have to reference your text file in a dollar sign expansion, with the prefix “Include”. Like this:
$(Include =..\includes\DBConnect.txt);
Note the use of the relative path (the ..\ ) which means that as long add the folder structure of the .txt file and .qvw remain together the files can be moved to a new location without anything breaking. Drive letters and UNC paths are both valid here also would you want to reference a fixed location.
Something to watch here is that QlikView will not raise an error if your include file is not found – it will simply continue without doing anything. This can be confusing at best when the code you thought would run does not.
The use of the Debug functionality in QlikView is particularly useful when using include files. Each include file opens on a separate tab in the debugger so it is quite easy to spot what code had come from where and ensure it is indeed getting executed.
Hopefully that has given you what you need to start using include files. I’m sure if you do use them you will be able to save yourself some time and bring a greater degree of consistency to your QlikView apps.
Steve,
Including externals scripts is indeed a useful feature. My only concern about them is security, as these external files are not protected with QlikView’s security for hidden scripts. For instance, I would be reluctant to keep in an external file a connection string to a database with confidential or sensitive information (even with scrambled password). Anyone who has access to such external script actually gets the ability to insert anything into loading script, including statements for partial reload and avoid QlikView security model. This may be not that important for small and medium businesses. But security policies of large enterprises (esp. banks) may find such inclusions insecure.
Hi Dmitry,
Many thanks for your comment. Security is well worth mentioning here. You are of course correct about features like Section Access and Hidden Script not applying to Include files. Code injection is indeed a concern. What I would say though is that your sever should be secured such that it is impossible for anyone to be able to download or modify any file on your server. QVD files can easily be read bypassing any Section Access and even a secured QVW file I would not like to think of being in the hands of someone wanting access to restricted data.
Security is definitely a major consideration with include files, but it is also so with everything else.
Hi Dmitry,
I am exactly going through the same problem of security with the external text file in an enterprise solution. Did you find any way to secure the connection, other than making sure the server is secure?
There was a mention on scrambling the password within the external text file, any idea on how this can be done? Would really appreciate if you could share what you have done in this aspect, thanks!
Locking down your file system to protect your files is not optional. If someone can get hold of your QVW they will have the opportunity to hack into it. If you do want to scramble the string I would suggest that you look on-line for a command line encoder, you can then put a call to this in your load script. This will mean that you need to allow external scripts in your load script though. I suspect that there are on-line encryption/decryption APIs – but then you are sending your connection details off to someone else’s computer to be processed.
I’d like to add two small things: you can also use UNC paths; I keep some scripts for date logic centralized on the QlikView server. Also, it breaks if you have any whitespace between the equals sign and the path. This little gotcha gave me some gray hairs back in the day.
Wow thanks!!! You just saved me a load of b*** ache with that little nugget of info regarding the white space.
Hi Oli. Glad you found it useful!
Thanks for your thoughts on this Jon. Sharing includes between many files is what can make them really valuable. See my blog post on Hidden Features to find out about the Must_Include statement – which personally I think offers more resilience to scripts.
Thanks for the post. I do have a rather big script file, but have the following problem.
When I define a variable that includes a variable, QV replaces the $(variable) with the actual value during import.
SET vKPI_AATotalCost = ‘=Sum({$<RMonth={"} If(AcctCode >= 6000 AND AcctCode <= 6999, InvAmount))';
Here for example the $(vActualMonth) is replaced with the value of that variable during import. How can I prevent that and define the variable as it is?
This functionality can be quite useful at times – where you want it to put in a fixed value – other times you want to avoid it. The simplest way to not have it do the replace is use a different character for the $ symbol, and then replace it afterwards, ie:
set vMyVariable = sum(¬(vSelectedMeasure));
let vMyVariable = replace(vMyVariable, ‘¬’, ‘$’);
If you use a let for the initial creation of the variable you can do it in one go, but the set is useful as you can have single quotes and carriage returns in your variable definition.
Hope that helps.
Hey Steve,
I’ve another question regarding the Include statement. What happens, if two two instances of qlikview access the txt at the same time. F. ex. there are running two scripts at the server and by accident the both access the include script at the same time?
Will both scripts fail, becaus they have no exclusive access to the txt file?
Thanks a lot in advance:)
This scenario shouldn’t cause an issue, as neither app will be writing the file so will not need exclusive access to it. The best way to find out would be to try it out. You could put a wait statement in the include file, to ensure it was open by both apps at the same time.
Hi Steve,
I can use two includes in same script, but can i use any variable in one archive and use this same variable another?
thx! hugs!
Unless you are using sub routines all variables are globally scoped, regardless of whether they are created in an include file or not.