Tuesday, 22 January 2013

QViewer QlikView Debugging Trick

Dmitry, who developed QViewer, posted a great little trick for seeing the contents of a resident table whilst debugging your script in QlikView. You can view his original post here but I thought I'd give you a quick step-by-step of how to make it work.

The first thing you need to do is to download and install QViewer if you haven't already. If you don't know what QViewer is, check out my earlier review post. You an download it from the EasyQlik.com website and best of all there is a free version. The only limitation is that it will load only the first 10,000 rows from the QVD file. This is normally more than enough for most applications and especially when debugging. If you use QViewer a lot then the full version removes this limitation for $45 (USD) (and will also help to encourage Dmitry to continue his great work no doubt).

You will be calling QViewer from within your QlikView script and so you need to change the privileges of your QlikView script to allow it to execute external programs. By default this is prohibited. In the QlikView Script Editor, select the Settings tab at the bottom left and ensure the "Can Execute External Programs" check box is ticked.

Next we need to add the small subroutine below towards the start of your script:

SUB Inspect (TableName)
    LET LocalAppDataPath = GetRegistryString('HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders', 'Local AppData');
    LET QViewerPath = '$(LocalAppDataPath)\EasyQlik\QViewer\QViewer.exe';
    STORE $(TableName) INTO [$(QvWorkPath)\~$(TableName).qvd] (qvd);
    EXECUTE "$(QViewerPath)" "$(QvWorkPath)\~$(TableName).qvd";
    EXECUTE cmd /c del "$(QvWorkPath)\~$(TableName).qvd";
    LET QViewerPath = null();
    LET TableName = null();

This subroutine is slightly different to the one Dmitry posted but the principle is the same, I have tweaked it slightly to make it work for most people so you can simply copy and paste the above subroutine into your script. If you didn't install QViewer into the default path you can change the QViewerPath variable to point to the path you installed it to.

That's all the set up done. We can now use the subroutine and QViewer to inspect the contents of tables during the script run. To do this we need to add the following line after the table we want to inspect has been loaded:

CALL Inspect('Transactions')

QlikView will store the passed table (in this example 'Transactions') into a temporary QVD file. It will then call QViewer to open the QVD file for you to view it's contents.

QlikView will automatically pause the script whilst QViewer is open and as soon as it is closed QViewer, the temporary QVD file will be deleted and QlikView will continue with the rest of the script.

A great little trick for checking the structure and contents of resident tables when debugging your scripts.