I am trying to get my script to reload in QlikView without success. I have added a new field (named Litres) to my most recently generated QVD files. However, my older QVD files do not have this Litres field which therefore causes the script to error out with the "Field not found" error.
I am trying to do the following:
- Search for all the fields in the QVD
- If the
Litresfield exists, then load the data into QlikView. - If not, create the field with a value of zero and continue.
The above should not result in an error.
My current script is below:
/* 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 */
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);
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
Thanks in advance!
Best Answer
You can use the
FieldNumberfunction to determine whether a field exists in a table. For exampleFieldNumber('MyField', 'MyTable')returns the position ofMyFieldwithinMyTable. IfMyFielddoes not exist inMyTablethe 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: