Thursday, September 4, 2008

Checking for errors when a report reloads automatically

In a recent posting (Click here to read it) we discussed ways to run QlikView from the command line as part of an automated script process. If you run the reload process automatically then you're faced with an issue: what should happen if an error occurs during the reload? Even a report that has reloaded successfully dozens of times before can run into an error if a file is missing or the database is down or if someone changed the title on a spreadsheet column (that's the one I seem to trip over most often).
If you're going to sit at your desk in front of the computer and watch it run then you don't have to do anything -- any loadscript error window that opens up can be clicked and the report can be cancelled if necessary. But, if the report is going to load unattended without anyone watching then consider these actions:

Check the Generate Logfile box on the General tab of the Document Properties window (select Settings->Document Properties from the menu - see the picture above). This is often a good idea whether you are reloading the report automatically or not. It causes QlikView to generate a text file audit trail of what happens as the loadscript runs. It captures the number of rows returned from queries and which branch an IF statement takes, it shows the expanded value of document variables that are used in commands and expressions and it shows the date and time for each command which allows you to analyze the slow-running parts of the loadscript. The logfile will be named just like the report file but with a ".log" added to the end. For example, if your report file is named Prior_Yr_Sales.qvw then the log file will be named Prior_Yr_Sales.qvw.log and it will be stored in the same folder as the report file. Open the log file with Windows Notepad or Wordpad. You can search for errors in the log file by using the Notepad or Wordpad Search function and search for Error:

For a report that must reload unattended (with no human folk watching) add this line to the top of your loadscript:
  SET ErrorMode=0;
That will tell QlikView not to open a loadscript error window when an error occurs (which would wait for someone to click OK). Instead the loadscript will go through all of the commands and do the best it can to execute all of them.

From your .bat or .cmd script you can have the script search for any lines containing the text Error: and perform an action if it is found. Here's an example:
rem Initialize the log file with xxx
echo xxx > c:\RPTS\Inventory.qvw.log
rem Now, run QlikView and reload
"c:\Program\QV.exe" /r /vbatch_flag=1 c:\RPTS\Inventory.qvw
rem Look through the log file and write
rem a note to Joe if we find an error
Type c:\RPTS\Inventory.qvw.log Find /c "Error:"
If errorlevel 1 echo %date% %time% "Error occurred" >> c:\Notify_Joe.txt

No comments: