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.