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.

Answer 1

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).