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).
You can use the FieldNumber
function to determine whether a field exists in a table. For example FieldNumber('MyField', 'MyTable')
returns the position of MyField
within MyTable
. If MyField
does not exist in MyTable
the function returns zero.
You can adapt this to your advantage by loading all fields from your QVD into a temporary table and then checking to see if this table contains the field. If it does, you can continue loading. If not, you can simply set the field to zero.
I have adapted your script and inserted an IF for this purpose:
/* get all sales csvs */
sales:
load 2014 as Year
AutoGenerate 0;
set FilePath = ..\..\SourceData\qv-sales*.csv;
for each File in filelist('$(FilePath)')
/* load qvd file if it is newer than csv file */
temptable1:
first 1 LOAD
*
FROM $(File)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
IF FieldNumber('Litres' , 'temptable1') <> null then
SET "Litres";
SET "Litres LY" ;
SET dyncode2 = P-Value;
SET dyncode3 = O-P-Value;
SET dyncode4 = P-Value LY;
SET dyncode5 = O-P-Value LY;
SET dyncode6 = P-Qty;
SET dyncode7 = P-O-Qty;
SET dyncode8 = P-Qty LY;
SET dyncode9 = P-O-Qty LY;
ENDIF
DROP TABLE temptable1;
let qvdFile = replace('$(File)','csv','qvd');
if (QvdCreateTime('$(qvdFile)') >= FileTime('$(File)')) then
/* load qvd file if it is newer than csv file */
fieldcheck:
FIRST 1
NOCONCATENATE
LOAD
*
FROM $(qvdFile) (qvd);
if FieldNumber('Litres','fieldcheck') = 0 then
drop table fieldcheck;
sales:
Concatenate (sales) load
"GP",
"O-GP",
0 as "Litres",
0 as "Litres LY",
"GP LY",
"O-GP LY",
$(dyncode2) as "P-Value",
$(dyncode3) as "O-P-Value",
$(dyncode4) as "P-Value LY",
$(dyncode5) as "O-P-Value LY",
$(dyncode6) as "P-Qty",
$(dyncode7) as "P-O-Qty",
$(dyncode8) as "P-Qty LY",
$(dyncode9) as "P-O-Qty LY" from $(qvdFile) (qvd);
else
drop table fieldcheck;
sales:
Concatenate (sales) load
"GP",
"O-GP",
"Litres",
"Litres LY",
"GP LY",
"O-GP LY",
$(dyncode2) as "P-Value",
$(dyncode3) as "O-P-Value",
$(dyncode4) as "P-Value LY",
$(dyncode5) as "O-P-Value LY",
$(dyncode6) as "P-Qty",
$(dyncode7) as "P-O-Qty",
$(dyncode8) as "P-Qty LY",
$(dyncode9) as "P-O-Qty LY" from $(qvdFile) (qvd);
endif
else
/* create temp store key1 & key2 are manually handled synthetic keys */
/* Dummy field is to prevent autoconcatenation problems. */
temptable:
noconcatenate Load
"GP",
"O-GP",
"Litres" ,
"Litres LY",
"GP LY",
"O-GP LY",
$(dyncode2) as "P-Value",
$(dyncode3) as "O-P-Value",
$(dyncode4) as "P-Value LY",
$(dyncode5) as "O-P-Value LY",
$(dyncode6) as "P-Qty",
$(dyncode7) as "P-O-Qty",
$(dyncode8) as "P-Qty LY",
$(dyncode9) as "P-O-Qty LY",
autonumber(1) as dummy1
FROM $(File) (ansi, txt, delimiter is ',', embedded labels);
/* create qvd file from temp table */
if (ScriptErrorCount = 0) then
Store temptable into $(qvdFile);
endif
sales:
concatenate("sales") load
"Litres",
"Litres LY",
"GP LY",
"O-GP LY"
"P-Value",
"O-P-Value",
"P-Value LY",
"O-P-Value LY",
"P-Qty",
"P-O-Qty",
"P-Qty LY",
"P-O-Qty LY"
resident temptable;
/* drop temp table */
DROP TABLE temptable;
endif
next File
Best Answer
Pre calculating this in the load script would be recommended approach to improve UI performance on larger data volumes, and it also improves maintainability in that you can reuse the field throughout your UI without having the logic stored in multiple places (i.e. on multiple charts).
Without seeing your data structures it is difficult to give you the exact structure, however, assuming you have both fields available in an in memory table during the load, you should just be able to extend your load script to include the calculation as an additional field: