Today I will show you how to store variables and their definitions in an external file… and why you would want to do that.
To start with the last part, in my opinion it is important to apply calculations and business rules consistently across QlikView applications. Doing this manually takes a lot of time and is prone to errors. Besides that, it is just not a very fun task. Fortunately, a lot of this can be handled by setting up a multi tiered environment with one or more QVD layers (for more information on this, see Rob Wunderlich’s excellent “Understanding & Best Practices with QVD files” webinar and slides).
Sometimes however, you need to use calculations that cannot be (easily) pushed to a QVD layer, for example when calculating averages or using set analysis. How can you store those calculations in a central place so they can be reused?
The first step is to create an external file (or database) to store your variable definitions. In this example I have created an Excel file with 3 columns, Name, Expression and Comment. 
In the next step, the data is loaded into QlikView as a table and a little bit of script loops through all the rows and creates the variables.
And that is all there is to it! The variables that were defined in the Excel file have now been created in the front-end, ready the be used. An example QVW with the full script can be downloaded below.
Download the example QVW and source data
The only downside to this method is that the comments are not being written to the the comment section of the variable, but are instead stored together with the expression. Fortunately, I will be putting up a guest post that deals with this very subject in the near future, so stay tuned!
As always, if you have any comments or questions, please post them below.
29 Comments
Will this method work if the expression contains a reference to another variable? For example, we allow the user to select their unit of measure, such as Cases or Pounds, and store their selection in a variable. Then the expressions use that variable in calculations. So we end up with:
sum({$}Price*$(vMeasure))
I haven’t been able to load this type of expression from an external file because QlikView wants to interpret the vMeasure variable as it’s loaded.
It should. I had the same problem with Dollar-sign expansions getting interpreted by the script. The solution was not to use an intermediate variable to store the expression code in the script, instead I peek it straight into the variable.
Barry, Interesting solution. Can you post a sample code to exemplify your solution to avoid the interpretation in the load script ?
Hi Pablo,
The example QVW contains examples of Dollar-sign expansion in the variables that does not get interpreted by the script, but is written straight to the variable definition. Drop me an email via the contact form if you want to know more.
Cheers,
Barry
That expression got altered when I posted but I think you can see what I mean.
Yep, seems to work. Thanks!
Nice solution, and one I use myself. It can get a bit more involved where you have expressions that need single quotes in them. I have in the past switched these out with a substitute in Excel (to a ¬ or somesuch) and then replace them back with a char(39) in QlikView.
A further step is to have a table of expressions loaded in QlikView that cant then be selected – so you can have a single chart for which you can pick the expression.
Agree about the single quotes, those can be a real hassle. QlikView could use an unescape function.
I’ve used the table of expressions to create a cycle group for expressions as well. Might be a nice subject for a short blog sometime.
Hi Barry,
Great solution! Do you know a way to sync the variables between the excel and the qvw’s > if I remove a variable in the excel file it will continue to exist in the qvw isn’t it?
best regards
William
Hi William,
You could write a macro that checks the existing variables to the ones that are being loaded and removes everything that does not belong. Not a pretty solution though, especially if you have application specific variables or variables that are being generated as part of your time dimension. Something to think about 😉
Cheers,
Barry
Nice know-how! I have been looking for some thing similar to this for some time today. With thanks!
Hi Barry,
This is a great tip! Always found it a big hassle to administrate and keep track of all variables in the QlikView interface
Thanks!
Renco
If you don’t want to be quite so fancy you could just use an include file.
I think the key advantage is the ability to store the formulas AND DESCRIPTIONS in a table and then present them in a list box on your applications.
Great post Barry!
One convention I try to follow when using this technique is to wrap the entire expression (in the Excel cell) with parentheses. I do this to help manage operator precedence. Example:
sum(Price * Amount) –> ( sum(Price * Amount) )
That first example was trivial, but you can see more relevance in this example:
( sum(Price * Amount) / sum(NbrOfUnits) )
So now if this variable is used in the denominator of an expression, the “sub” denominator sum(NbrOfUnits) will be correctly handled.
Cheers,
Bill
Good tip! I’ll add it to my coding conventions.
Awesome blog and post barry!
Very curious about the Daft Punk image on the post, tried to find the association but maybe it’s just because you’re a fan? 🙂
Hi Francois,
Thanks.
Regarding the Daft Punk image, I definitely enjoy Daft Punk* but the association is probably a bit too far-fetched. This was my third post in a row about QlikView variables, I thought the subject was getting a bit monotonous but figured: One more time…..
Cheers,
Barry
* My interest in Daft Punk was recently rekindled by these funky 8 bit mixes: http://dachip.com/
Can anyone please help me in understanding the code which is used to writeback the data from qlikview to the database.I do not have any idea on vbscript or javascript.
It would be a great help
Hi Barry,
I would like to define a variable that is calculated via two other variables. Can I simply define the expression like $(varX)*$(varY) in my excel spreadsheet?
Thanks!
Simon
Hi Simon,
Yes, you could do exactly that.
Kind regards,
Barry
Hi Barry,
thanks a lot for your reply. You might be interested in this one:
https://community.qlik.com/message/826367
Greetings from Germany
Simon
Hi Simon,
If you change the // comment for /* and */ comments then it should work.
Kind regards,
Barry
Thanks again, Barry! That did the job.
Hi Simon,
The best solution you have provided for people like us, who were trying to use expressions which are in Excel. I myself is having little different issue. I have row wise expressions i.e. all the rows in excel sheets (about 24 rows & 8 columns) have different expressions, so how can I use your solution. Below is an example.
Inupt
https://community.qlik.com/servlet/JiveServlet/downloadImage/2-839268-95248/pqcdsm.JPG
Required Output :
https://community.qlik.com/servlet/JiveServlet/downloadImage/2-839268-95249/pqcdsm1.JPG
Hi Barry,
We have similar requirement to do in Sense. Do you have a Sense solution I can lookup? Also, as I’m new to Qlik world, it would really help if you could send me steps on how to deploy the solution & other use cases like Webservice integration etc.
Again, the solution you have provided here is great. Thank you!
Hi Barry
I have create my excel sheet with the variables but when I use the variable in a SQL LOAD Where Clause it seems to pull in the text instead of the actual value?
It is very help full to resolve my issue with Set analysis and Variables.
I appreciate your work.
I have a doubt,.
I have used externalizing variables to create the variables. Some complex calculations did not return results.
I have figured out the issue based on your sample attached here.
I have modified the For Loop as below, I got results for set analysis that uses a variable and the variable return results based on set analysis.
e.g.
Name | Expression
——————————————————————————————————-
monthtodaysetanalysis | only( {$} Month)
FebAmontv2set | sum({$}Amount)
$(FebAmontv2set) return results for the For loop used in creation of variables is as below :
—————————————————————————————————————-
// Create variables
for i = 0 to NoOfRows(‘Temp_Variables’) – 1
let vName = peek(‘Name’, i, ‘Temp_Variables’); // Name of the variable
let vComment = peek(‘Comment’, i, ‘Temp_Variables’); // Comment/description of the variable
let $(vName) = peek(‘Expression’, i, ‘Temp_Variables’) & chr(10) & ‘/* $(vComment) */’; // Expression
next i
—————————————————————————————————————-
I do not get results for the below For Loop
—————————————————————————————————————-
// Create variables
for i = 0 to NoOfRows(‘Temp_Variables’) – 1
let vName = peek(‘Name’, i, ‘Temp_Variables’); // Name1 of the variable
let vComment = peek(‘Comment’, i, ‘Temp_Variables’); // Comment1/description of the variable
let Expression = peek(‘Expression’, i, ‘Temp_Variables’) & chr(10) & ‘/* $(vComment1) */’; // Expression
let $(vName) = ‘$(Expression)’;
next i
—————————————————————————————————————-
The difference between the above 2 set of For Loop is
First For Loop uses only one statement like $(vName) to set expression directly.
Second For Loop uses 2 statements as below to set expressions
vExpression=…..
$(vName)=$(vExpression)
—————————————————————————————————————-
Could you please explain why the second set of For Loop not returning results.
Thank you very much.
Lux