Qlikview - calculate and use calculated variable in script

Question

As a new Qlikview user, I'm looking for the best way to create calculated variables, and variables based on calculated variables, in my data and use them in displays. My data is connected via ODBC.

For example, let's say I want a variable Rating based on the "Risk" variable in my dataset. The raw data contains a Risk variable that is "L" or "H". I would like to create an indicator, like Risk_H, that is 0 or 1 (if Risk='H'). Then I would like to create the Rating like "Rating = 1 + Risk_H*2". Can I do all of this in a script and have the variable Rating in my dataset?

When I try the above, I can create the Risk_H variable, but then I am not sure how to reference it in the script to calculate the Rating variable. I have read other posts that address using the load statement (Qlikview Calculated Fields with Load Script) but have been unsuccessful using calculated variables to create new variables.

Example code (which works):

SQL SELECT *,
case when (Risk = 'H') then 1
   else 0
   end as Risk_H
FROM [Data];

How can I create Risk_H in order to use it in the same script, like the below? In other settings, I would use something like "calculated Risk_H" to refer to it.

SQL SELECT *,
case when (Risk = 'H') then 1
   else 0
   end as Risk_H,
(10 + Risk_H*2) as Rating   // Qlikview says it can't find Risk_H
FROM [Data];

I've tried creating Risk_H in a load script, but Qlikview doesn't recognize Risk_H in a later SQL statement. I've also tried creating a table with Risk_H , and pulling the data from that table. And in reality I'm trying to create 10+ indicators, not just one, so nested case statements aren't the answer.

EDIT: I'm told that resident tables may be the answer to performing calculations. If you can provide syntax for this using tables connected via ODBC that may answer the question.

How are you trying to access Risk_H? If you want to get a value from a field that has been loaded use >>let var = FieldValue('Risk_H',1);< — seebach, May 02 '14 at 11:31
And remember that what you in the script should only be data modelling and cleansing, all calculations are done in the GUI. — seebach, May 02 '14 at 11:32
Thanks for the reply. I'm not accessing Risk_H in the sense that my original data doesn't have it, I'm creating it there in the script. When you say "all calculations are done in the GUI," do you mean that I should not create a variable as a combination of other variables (i.e., Rating) in the script? It seems like if I calculate Rating in an expression I have to put that expression into any object where I use Rating. — MusicAndMath, May 02 '14 at 12:49
This is a SQL issue since Risk_H is not a column you have to use the case expression twice. — ralfbecher, May 05 '14 at 15:27
The idea is that you should do calculations in the GUI. If you do the same calculations over and over, then there is a benefit in preparing it in the script. But if its complex, when it might be difficult. If you use it over and over, then you can create a variable, that contains the expression and place that in your charts. To evaluate a variable a in expression use =$(variable) — seebach, May 09 '14 at 06:52

score 1 · Answer 1 · answered May 07 '14 at 12:24

It appears that your second Select statement is not valid SQL so as a result QlikView will complain that it cannot find Risk_H. You could try a more complicated SQL query with a sub-query to resolve this, or you could use a resident load in QlikView as follows:

Source_Data:
SQL SELECT *,
case when (Risk = 'H') then 1
   else 0
   end as Risk_H
FROM [Data];

Calculated_Data:
NOCONCATENATE
LOAD
  *,
  (10 + Risk_H*2) as Rating
RESIDENT Source_Data;

DROP TABLE Source_Data;

You also mentioned that you have around 10 indicators that you wish to use, so I agree, a case statement would probably not be a good idea. You can move this part into QlikView as well if you like using a MAPPING load and the ApplyMap function as follows:

Indicator_Map:
MAPPING
LOAD
  *
INLINE [
  Risk, Value
  H,    1
  I,    2
  J,    3
];


Source_Data:
SQL SELECT *,
case when (Risk = 'H') then 1
   else 0
   end as Risk_H
FROM [Data];

Calculated_Data:
NOCONCATENATE
LOAD
  *,
  (10 + (ApplyMap('Indicator_Map',Risk, 0) * 2)) as Rating
RESIDENT Source_Data;

DROP TABLE Source_Data;

I added a couple of extra entries for your Risk "indicators" to give you an idea. Of course, the table doesn't need to be inline, it could come from another SQL statement, other file etc.

In the above example, what happens is that the Risk field's value is supplied as a parameter to the mapping table Indicator_Map which then returns the associated value. If no risk value is found, it returns 0 (the third parameter).

Qlikview - calculate and use calculated variable in script

1 Answers1