Choosing the Tables to Keep After a Binary Load in QlikView

Written By Reiss Moore, Edited By Lewis Fogden
Mon 21 November 2016, in category Business intelligence



For those of you familiar with QlikView, you should know the Binary Load. A Binary Load allows you to rapidly load all of the data from one QVW into another, with the limitation that no variables are transferred across.

One challenge I came across when I started developing Lite versions of our main application (which was a significant size - roughly 800MB – with a fairly complex data model), was that I only wanted to keep tables from the original app that were relevant to the Lite version.

The traditional way to do this is to include a long list of drop statements to remove the unwanted tables. However, the limitation with this method is that you must take care when a new table is added to the original QVW, and ensure that you insert a new DROP TABLE statement in the resultant QVW.

While it isn’t the end of the world to keep doing this, I wanted to think of a way that I could chose which of the tables to keep, and drop all of the rest instead. The solution I came up with is contained in the following code snippet, which can go anywhere after your Binary Load statement

// An inline table is used to list the tables you want to include from the Binary Load
// The For..Next loop creates a variable that lists all of the tables loaded into the QVW
vTables = TableName(0);
FOR i = 1 to NoOfTables() - 1
    LET vTables = vTables & '|' & TableName(i);

The following steps occur in the load below:
1) Subfield splits the table variable into individual rows
2) Use a WHERE NOT EXISTS to exclude tables listed in the previous inline table
3) Concatenate the table names given in the previous step to create a string of all the tables to drop

'['&Concat(DropTables,'],[')&']' as DropTables
WHERE NOT EXISTS(Table, DropTables)
subfield('$(vTables)','|') as DropTables

// Assign the drop table string to a variable
LET vDropTables = peek('DropTables',0);

// Use the string variable to drop all tables except those opted to keep
DROP TABLES $(vDropTables);
DROP TABLE DropTables;

It would be great to hear if anyone can either think of ways to improve this method, or has an alternative, as there are always a million ways to do one thing in QlikView.