Using Dynamic Updates in QlikView 11

Dynamic Updates is another one feature that makes difference between QlikView and other popular BI tools even bigger (see also "Really, is QlikView a BI tool?"). If you haven't heard about it -- it allows adding new or modifying existing data right in the in-memory data model of a QlikView application without re-running loading script. While this feature can be used (to some extent) for real-time updates, in my opinion the main benefit from it lies in a different area -- it enables creating closed-loop workflow with operational applications. It means that not only can you take data from operational applications (e.g. Salesforce or SAP), but return some user-generated output from QlikView into these applications as part of a single workflow. For example, in one of my applications a user can get a list of pending orders from logistic system, create batches from these orders based on some filters and selections in QlikView and then return these batches for delivery into the logistic application.

Dynamic Updates can be performed in two ways -- using VB macros (starting from version 10) and using Actions (starting from version 11). Using actions is a more preferable way (as VB macros are losing more and more favor with QlikTech). Unfortunately, documentation about Dynamic Update Actions is very scarce and there are lots of limitations and side effects, so I tried to collect here some practical hints and tips how it can be used:

Actions utilize SQL-like syntax (however very very limited). E.g.:

INSERT INTO * ('Batch ID', 'Order ID') VALUES (1, 'ABC123');

UPDATE Batches SET WrongBatch = 1 WHERE "Order ID" = null();

DELETE FROM Batches WHERE -1;

These commands look like SQL but here similarities end. Some of limitations:

• You can use equal sign (=) in WHERE clause. You cannot use greater (>) or less (<) signs or not equal (<>)
• You can combine conditions using AND or OR in WHERE clause. You cannot use NOT
• Check for nulls is done differently than in ANSI SQL (see example above)
• Notice different syntax for field names with spaces

I had problems with inserting dynamically several values by one command despite it's possible with hardcoded command. So I ended up dynamically generating several commands separated by semicolon using dollar-sign extensions. E.g.:

$(=concat(DISTINCT 'INSERT INTO * (OrderID) VALUES ($(vNewBatch),'&[Order ID]&')', ';'))

On QlikCommunity there is excellent demo application made by Matthias Dix that shows basic Dynamic Update commands and their equivalent using VB macros (full thread here).

You should also keep in mind that there are some significant side effects from using Dynamic Updates which often lead to unpredictable behavior. This article explains very well these side effects and how to deal with some of them. I struggled with failed variable assignments (Set Variable action) in the same action batch with Dynamic Update, unexpected freezes, and that any Dynamic Update command for some reason triggers all OnSelect actions. Finally I came to this workaround:

• Put all variable assignments (Set Variable action) before Dynamic Update command otherwise new values of these variable won't be visible in expressions.
• Use some dummy command right after Dynamic Update to fight freezes, e.g. Set Variable for dummy (i.e. not used) variable, or even better ...
• ... use Selection -> Back action right after Dynamic Update command to compensate triggering all OnSelect events (won't have effect on VB Macros tied to OnSelect).

UPDATE

Keep in mind that for server-hosted applications all changes made by Dynamic Update apply immediately for all users that are working with the applications. It means that you need to make sure that actions of different users working simultaneously won't interfere. It can be achieved, for instance, by adding user name for each record created by Dynamic Update and then using set analysis expressions to limit records to only those which are required for the user. But in any case, extra care should be taken to manage correctly all possible concurrency cases.

QViewer rebranded, got paid version

A few significant changes happened to QViewer -- viewer for QVD files which I wrote a few months ago and still continue developing.

First of all, QViewer has been re-branded as EasyQlik QViewer and got its own web-site http://easyqlik.com (which however doesn't differ much from the old one).

Second major change is that QViewer has now two versions -- free and full. Free version is limited to 10'000 rows which is still quite enough to do some data profiling. Full version is paid (license key can be purchased for $45) and unlimited. License is personal (i.e. limited to a person, not organization) and has no limitation by number of installations -- you can use QViewer on as much computers as you need. In 8 months the demo version was download almost 1'000 times so it gives me the hope that someone found it as much as useful as I do :) However, further development of QViewer in terms of features, usability and functionality will directly depend on number of licenses purchased as this will show if QViewer is really useful tool or not.

Third change is that finally I got rid of troublesome ClickOnce installer which caused lots of problems with installation and even more with uninstallation. Now QViewer comes with standard neat NSIS-based installer and actually is a portable application. It means that you don't necessarily need to run installer if you want to move QViewer to a different machine. Once it has been installed, you can simply copy qviewer.exe to other machine or add it to your portable apps collection on USB-stick. However, running installer has advantage of automated association with .qvd file extension, which can be problematic otherwise. And as previously, QViewer requires installed .NET v4.0 or above -- keep this in mind when using QViewer in portable mode.

Future development plans include adding sort functionality for listboxes and metadata tables, and design of custom data grid. The latter should significantly decrease load times as the current implementation uses standard DataGridView which is so slow that only initializing it actually takes major part of total load time.

And of course, if you have feature suggestions -- don't hesitate to give me a shout by email or in comments here.

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