Enhanced Store and Drop

A very common use case in QlikView is to load a table, do some transformations, store the table to a QVD file, then drop the table.  If you have many tables in a script, writing out the whole

Store XXX to YYYY.qvd;

and then

Drop Table XXX;

Gets a bit wearing after a while.

My solution is to use a subroutine in the QlikView script like this:

Sub StoreAndDrop(vTableName)

Trace Store and Drop $(vTableName);

// Store the table to QVD
Trace Storing [$(vTableName)] to [$(vQVDPath)\E_$(vTableName).qvd];
Store [$(vTableName)] into [$(vQVDPath)\E_$(vTableName).qvd];

// Drop it
Trace Dropping Table [$(vTableName)];
Drop Table [$(vTableName)];

End Sub

This, very simply, replaces the repetitive code and I can call it like this:

Call StoreAndDrop('Customer');

A perfect use for a subroutine!

Quite often, we might want to capture additional information about the load such as how long the load took and how many records were loaded.  All of this can be captured using the sub routine.  Like this:

LET vTotalTablesLoaded=0; 
LET vScriptStartTime=now(); 
Let vLoadStartTime=now();

Sub StoreAndDrop(vTableName)

Trace Store and Drop $(vTableName);

Let vTotalTablesLoaded=$(vTotalTablesLoaded) + 1;
Let vLoadEndTime=Now();

// if needed
UNQUALIFY *;

TablesLoaded: // replace with join(TablesLoaded) after first table
LOAD
   '$(vTableName)' as [Loaded Table Name],
   NoOfRows('$(vTableName)') as [Number of Records Loaded],
   '$(vLoadStartTime)' as [Load Start Time],
   '$(vLoadEndTime)' as [Load End Time],
   Time(TimeStamp#('$(vLoadEndTime)')-TimeStamp#('$(vLoadStartTime)')) as [Table Load Time]
AutoGenerate (1);

// if needed
QUALIFY *; 
UNQUALIFY [Join_*], [*ID];

// Store the table to QVD
Trace Storing [$(vTableName)] to [$(vQVDPath)\E_$(vTableName).qvd];
Store [$(vTableName)] into [$(vQVDPath)\E_$(vTableName).qvd];

// Drop it
Trace Dropping Table [$(vTableName)];
Drop Table [$(vTableName)];

Let vLoadStartTime=now();

End Sub

Thanks to the very tallented Carly McClintock for inspiring this post.

---

Stephen Redmond is author of QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond