December 15, 2012

How to look inside resident tables at any point of loading script

What do you usually do when you need to look inside a resident table somewhere in the middle of loading script? I guess you store it as QVD, then load this QVD in a separate temporary QlikView application, then create table box or something else to browse the table.

Here is small trick how you can make your life easier using QViewer:

Insert in the beginning of the loading script this short snippet:

SUB INSPECT (T)
LET vPathToQviewer = 'C:\User\Dmitry\AppData\Local\EasyQlik\QViewer\';
STORE $(T) into [$(vPathToQviewer)temp_qvd.qvd] (qvd);
EXECUTE "$(vPathToQviewer)qviewer.exe" "$(vPathToQviewer)temp_qvd.qvd";
ENDSUB

And then simply insert calls to this function in any place of the loading script where you need to inspect table in its current state. For instance:

CALL Inspect('Orders');

When execution of the loading script will come to this line it will open QViewer showing the table. Script will continue running as soon as you close QViewer.

This is convenient technique that allows inspecting resident tables after every transformation like joins, concatenate loads, etc. You can have as many inspection points as you need during a single run.

A few more tips:

You may receive Security Alert from QlikView because of EXECUTE statement as depicted below. Just press Override Security button. Or you can change security settings in the bottom of the Script Editor window.


You can put this snipped into some file (e.g. debug.qvs) and then include this file when you need to do debugging of some application. Also you might want to save inspected tables under their own names in some temporary folder -- just modify the snippet accordingly or add a new similar function, e.g. InspectSave.

Any more ideas?

UPDATE 1

+Donald Hutchins made a good point -- he offered to store temporary QVD file not in QViewer's folder but in the working folder of the appliction which loading script is being executed. I think it makes a lot of sense because of less problems with access rights and better fit for parallel use. Here is his variant with my minor changes:

SUB INSPECT (T)
    STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd);
    EXECUTE "C:\.......\QViewer.exe" "$(QvWorkPath)\~$(T).qvd";
    EXECUTE cmd /c del "$(QvWorkPath)\~$(T).qvd";
ENDSUB


UPDATE 2

+Matthew Fryer proposes one more variant of the Inspect sub in his blog QlikView Addict.


UPDATE 3

You can use EasyMorph for exactly the same purpose -- inspecting resident tables. Pros: with EasyMorph you can do more than just viewing a QVD -- you can filter, aggregate, calculate new columns, join other tables, etc. Also the free EasyMorph doesn't have the limit of 100K rows as the free QViewer does. Cons: EasyMorph opens QVD slower than QViewer as it needs to convert them to its internal format. Also it's less memory efficient when it comes to large QVDs.

The only difference in script would be additional /load key:

SUB INSPECT (T)
    STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd);
    EXECUTE "C:\.......\morph.exe" /load "$(QvWorkPath)\~$(T).qvd";
    EXECUTE cmd /c del "$(QvWorkPath)\~$(T).qvd";
ENDSUB