Powerful variable editing with Sublime and inQlik tools
Swedish version:: Kraftfull variabel-hantering med InQlik Tools
Centralized expression handling is a topic that rise in an organization when their QlikView environment matures. There are many reasons why one wants to move expressions and variables from individual QlikView documents into a centralized location. Often it’s about ensuring that a single kpi is calculated in the same manner for all graphs and tables in all QlikView applications. When its time to edit a kpi calculation then the change will only need to be done at a central location and not in every object that uses it. The objects refers to this variable instead of using the hard coded expression. This increases quality and reduces time needed to implement the changes. In other scenarios it is the need to let other parts of the organization take ownership and responsibility for the kpi and calculation logic, not IT.
The use of external variables may also speed up your environment by increasing the use of the QlikView and Qlik Sense cache. The cache will only be reused when two identical written expressions are used, but with small diffrences between expressions the cache breaks. Read the Qlik DesignBlog - The QlikView Cache to get better insights.
I have seen several creative ways to lift out QlikView expressions from documents into a central place. I’m guilty of several of those solutions. I get the feeling that each developer or development team have their own method for handling this issue. The main principle and the desired result is always the same, expressions and variables are stored centrally and used by QlikView on across multiple applications.
The Qlik Variable Standard
In QlikView Deployment Framework (QDF), there is a, by Qlik, recommended standard for variable handeling. It contains both a suggested name standards together with a methodology and support for external variable handling. By using the Deployment Framework Editor you can create and manage variablefiles that by easy means can be imported into a QlikView document using the sub ‘LoadVariableCSV’ that is included in the framework.
Management and manipulation of the QDF variable files are a feature in the Deployment Framework Editor interface. This interface is sufficient during maintainance and overview, but for my frequent use ande edits use during the development phase I’ve found a better alternative.
InQlik expression editor for Sublime
InQlik expression editor is part of the open source project InQlik Tools, an add-on package to Sublime Text.
Using InQlik tools variables are written with a simplified YAML-like syntax in the Sublime Text editor. In addition to saving the YAML-like document InQlik Tools also saves a copy of the expressions into a QDF compatible variable file.
--- set:vL.Income definition:sum(income) label:Income comment:Total income. ---
This syntax feels natural and easy, compared to a csv-file. I feel more effective in my work working this way compared to editing the csv directly or using the QlikView Framework Editor. The output of the YAML-like syntax above will result in a csv like the text below:
Variable Name, Variable Value, Comments, Priority SET vIncome, sum (income) ,, SET vIncome.Comment, total income. ,, SET vIncome.Label, Income ,,
It is possible to change output from csv to native QlikView script code (qvs), for those who are not using the QDF but want a centralized variable handling. The native qvs-file can be inluded into the script either by include or just copy-paste of thest, as the qvs result looks like this:
SET vIncome = sum(income);
SET vIncome.Comment = Total income.;
SET vIncome.Label = Income;
Dollar expansion
In my own previous attempts in handling external variables i often wrestled with expressions using dollar expansions. QDF-handles this great, but also the inQlik tools qvs exports handles this with seemingly without any problems.
--- set:vL.sumDynExp definition:sum([$(=DynExp)]) ---
The expression declaration above will result in the native qvs variable declaration as written below. Notice how it handles the dollar ($) problem.
let vL.sumDynExp= replace(replace('sum([@(=DynExp)])','~~~', 'chr(39)'), '@(', chr(36) & '(');
Reference to other variables
InQlik expression editor are able to do something that neither QlikView Framework editor, notepad or excel can do without carrying out a small development projects in advance. When using InQlik variable declaration you are able to refere to other variables and during export to qdf-csv or qvs expand thes references such that the referenced declarations is exported.
In the example below, I let the third expression be declared with references to the two prior.
--- Set: vL.income definition: sum ([income]) --- sets: vL.expense definition: sum ([Expense]) --- sets: vL.profit definition: $ (vL.income) - $ (vL.expense) ---
By letting InQlik tool expand the variables during export I get vL.Profit saved as sum ([income]) - sum ([Expense]) . No more manual tracing down multiple layers of referenced variables when tracking down a bug!
Summary
Centralized expression handling has several advantages, both in terms of management, accountability. It can also be benificial in terms of system speed and utalization of the QlikView global cache.
Qlik are pointing all developers in the same direction with the introduction of a standard variable naming and handling. I think that this standard will fill the needs found in many scenarios when it comes to variable handling and expression centralization. I see no reason not to start using the QDF standard format for handling variables unless you already have invested in other methologies. It is an easy and open standard.
I prefer creating variable files using InQlik on Sublime, but you could easily use another system or stick to the Deployment Framework Editor that is shipped with the QDF. The reason I enjoy inQlik variable editor is the possibility to in a efficient way build and reuse expressions. The declaration syntax is easy to write and read and I use the same syntax whether I choose to export to qdf format or plain qvs script.