In this post, I’d like to share a technique for managing tables that I’ve used in both QlikView and Qlik Sense apps. Very often you’ll have an app that contains data that you binary load into one or more apps. Depending upon the number of tables and size of the data, that app can be quite large. Usually, you’ll need all the data in the source app, but what if you only need one table, or just a few?
Here’s a script that enables you to binary load your data and then quickly drop all the tables except the ones you need.
Let’s assume that we have an app containing many tables. We’d like to drop all the tables and keep only tables names States, Customers and Sales. The script below does this. All we need to do is set the variable “vKeepTables” to a string containing the table names we’d like to keep, separated by semi-colons.
Follow the comments to see how it works.
Here’s a script that enables you to binary load your data and then quickly drop all the tables except the ones you need.
Lets assume that we have an app containing many tables. We’d like to drop all the tables and keep only tables names States, Customers and Sales. The script below does this. All we need to do is set the variable “vKeepTables” to a string containing the table names we’d like to keep, separated by semi-colons.
Follow the comments to see how it works.
// First, set the string of table names that we’d like to keep.
Set vKeepTables = ‘States;Customers;Sales’;
//Next, build a mapping table that contains the table names from the string above.
KeepTables _Map:
Mapping Load
SubField(‘$( vKeepTables)’, ‘,’) as KeepTableName,
1 as Tresult
AutoGenerate 1;
// Iterate through all the table names in the app. If the table name is not found in the mapping table
// then Drop it.
For vTableNum = NoOfTables() -1 to 0 step -1 ;
Let vTableName = TableName($(vTableNum)) ;
If ApplyMap(‘ KeepTables _Map ‘, ‘$(vTableName)’, 0) = 0 then
Drop Table ‘$(vTableName)’;
EndIf;
Next;
You’re probably wondering why I set a variable to the names of the tables that I’m keeping and then load it into a mapping table, instead of just doing an inline load of that table … you we’re wondering, weren’t you? The script is written such that is can be stored as an external script and then used via an Include (or better still, a Must Include) statement. That was anyone can use it. All they need to do is set the variable in their app and call the script.
What are your favorite utility scripts? Let me know in the comments. Or let me know what you’d like to know how to do.