Recently, one of our customers came up with this requirement: Create a QVD with an incremental load. But, then there came a problem-”How would I do an increment load on a QVD, when it doesn’t exist in the first place? “ (Wait, it will be confusing, if you don’t know about incremental load, check it here, http://www.learnallbi.com/incremental-load-in-qlikview-part1/ ) then I thought, if at all I could trap those errors and use them for my convenience, I’d be able to do this and there began my search for error handling in Qlikview.
To my luck, Qlikview did mention about it in their manual, not very detailed though. So I thought I would give head start to those who would be trying to do their own error handling in Qlikview like me.
My program is simple, I need to do an incremental load on a QVD (Let’s call it Stage 2 QVD), which stores snapshots of an existing data (Stage 1 QVD). So, I don’t need to use any unique identifiers for this type of incremental load, just concatenate new data with a new date-stamp and store it away.
So, first I will check whether Stage 2 QVD exists? If not, create it with first set of data. Oh, it’s already there? Then go take data from it and add new set of data from Stage 1 QVD, to it. There you go, quite simple!
So, now how would I handle that error, if that Stage 2 QVD is not there? Don’t freak out! Qlikview has it covered for us. First set ErrorMode to 0 in the script,
Set ErrorMode=0;
This is manual override for errors in Qlikview. Careful, now though, you wouldn’t be able to see any errors in your application from this moment on. Next as our requirement is to find whether Stage 2 QVD exists or not? for this we will write a simple IF ELSE statement.
So, IF ScriptError=8 or ScriptError=10, here ScriptError=8 is “File not Found” and ScriptError=10 is “Table Not Found”, So if either of these errors has occurred then create a Stage 2 QVD.
ELSEIF ScriptError=0 ( I have used 0 as No Error, as when no errors are there ScriptError will stay 0. It will only become 1 after finishing first statement execution or finishing the script ) which means if QVD exists then take data from Stage 2 QVD and append new data from Stage 1 QVD and store the same into that Stage 2 QVD which would replace existing QVD.
If any other error than mentioned occurs in the script, it will exit from the load.
Refer to Qlikview 11 reference manual for more detailed execution of these. I will quote error handling section mentioned in reference manual for your convenience here.
ErrorMode:
This variable determines what action is to be taken by QlikView when an error is encountered during script execution. By default (ErrorMode=1) the script execution will halt and the user will be prompted for action (non-batch mode). By setting ErrorMode =0 QlikView will simply ignore the failure and continue script execution at the next script statement. By setting ErrorMode =2 QlikView will trigger an “Execution of script failed…” error message immediately on failure, without prompting the user for action beforehand.
Example:
set ErrorMode=0;
ScriptError:
Returns the error code of the last executed script statement. This variable will be reset to 0 after each successfully executed script statement. If an error occurs it will be set to an internal QlikView error code. Error codes are dual values with a numeric and a text component. The following error codes exist:
1 No error
2 General Error
3 Syntax Error
4 General ODBC Error
5 General OLE DB Error
6 General XML Error
7 General HTML Error
8 File Not Found
9 Database Not Found
10 Table Not Found
11 Field Not Found
12 File Has Wrong Format
Example:
set ErrorMode=0;
load * from abc.qvw;
if ScriptError=8 then
exit script;
//no file;
end if
ScriptErrorDetails:
Returns a more detailed error description for some of the error codes above. Most importantly this variable will contain the error message returned by ODBC and OLE DB drivers for error codes 3 and 4.
ScriptErrorCount:
Returns the total number of statements that have caused errors during the current script execution. This variable is always reset to 0 at the start of script execution.
ScriptErrorList:
This variable will contain a concatenated list of all script errors that have occurred during the last script execution. Each error is separated by a line feed.
References : Qlikview Reference Manual v11