This lesson handles the general organisation and structuring of QlikView projects. Based on our longtime experience with software development and QlikView implementations, we here show our “Best Practice”.
As easement for our own projects we provided an online template which enables the development of stable BI-solutions following our standards.
- 00 CONFIG
- 01 Dataloader
- 02 Datastaging
- 03 Datalogic
- 04 Datamart
- << as DEV >>
The goal of these structures is to create a distinct line between productive and development systems – without an additional testserver. Only if all logics work with test structures, a migration to a productive environment can happen.
Working with variables
To develop a “company solution” it is useful to work with global variables, which everyone can use. This is going to work like the following:
//defining a welcome variable SET varHello = "Welcome!"; LET varUser = OSUser(); TRACE "$(varHello) $(varUser) :)";
To award global variables, QlikView offers the possibility to include external TXT-files into the current script and to execute them “right here, right now” via “include”.
The difference between LET and SET variables is the evaluation of the expression behind the “equal” symbol. LET immediately “calculates” the expression while SET saves the expression for use later on.
//implementation of external sources $(include=load_external_file.txt);
If variables have been defined in “load_external_file.txt” then they are now available.
Models for script blocks
Here are some exemplary script blocks which are recommended to integrate into every QlikView script.
/** * @author Philipp Frenzel <email@example.com> * @version 1.0 * @copyright Frenzel GmbH 2015 * @depends - list of scripts that need to be executed before * @provides - list of output files * @schedule - * * * * * in the style of unix cron */ //set the environment SET _ENV = "DEV"; SET _DEBUG = 1; //here we load the global config $(include=../../COMMON/00 Config/CONF_PATHS.txt);
Please keep in mind that depending in which environment you are situated and also that “include” has to be controlled from external sources. The debugger helps with this by showing external sources in seperated riders.
/** * @dataloader tbl_demo * @source OLEDB * @version 1.0 */ SET varTableName = "tbl_demo"; QUALIFY "*"; $(varTableName): LOAD *; SQL SELECT * FROM [$(varTableName)]; UNQUALIFY "*"; STORE $(varTableName) INTO "$(PATH_DATASTAGING)$(varTableName).qvd" (qvd);
For the completion of periodic patterns, the QlikView veteran Rob Wunderlich wrote a library which eases exactly these patterns. In our projecttemplate we permanently update it and we recommend you to use it as well.
This is how you integrate the library:
//at this point we integrate the qv-components $(include=../../COMMON/LIBS/QVComponents/QVC_Runtime/Qvc.qvs); $(include=../../COMMON/LIBS/QVComponents/QVC_Runtime/language/qvc_language_GE.qvs);
To help with this topic, QVWs, a comprehensive documentation, have been provided. We now get to know a few of the useful routines:
//at this point we write a comment into a LogFile CALL Qvc.Log('Here is our information',['level']); //level options: INFO, WARNING, ERROR - default *
//a bit complex but still useful - a "standard" calendar CALL Qvc.Calendar (vMindate, vMaxdate, ['CalendarTableName'], ['FieldPrefix'], [FirstMonth]);
Take one day to get an overview of the features. In our practical experiene you are going to save much time with doing this.
In the folder Libs/QVComponents/examples are examples for all functions if you are sill unconfident with using them.
- Download a “zip” from the project and build a “first” dataloader in the DEV index.
- Use the include function to gain access to “global” elements