Choosing the Tables to Keep After a Binary Load in QlikView

By Reiss Moore, Mon 21 November 2016, in category Business intelligence

Qlikview

  
@

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
KeepTables:
LOAD  INLINE [
Table
Orders
OrderDetails
];
// 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);
NEXT ;

/* 
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
*/

DropTables:
LOAD 
'['&Concat(DropTables,'],[')&']' as DropTables
;
LOAD 
DropTables
WHERE NOT EXISTS(Table, DropTables)
;
LOAD 
subfield('$(vTables)','|') as DropTables
AutoGenerate(1);

// 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.