Whilst working with a customer last week I had a conversation with one of their developers about dropping tables. The customer wanted to be able to drop all their temporary tables from their model at the end of the script. They used a naming convention in which their temp tables were always named ending in "-temp". Ideally we'd be able to do this using the inbuilt DROP TABLE statement in QlikView script, something like this:
DROP TABLES "*-temp";
Unfortunately this isn't supported and you must explicitly name each table in the DROP TABLE statement. I knew I'd solved this problem before, so after a little digging through old QlikView apps, I finally found the subroutine I'd written and I thought I'd share it with you all.
First of all you need to define the following subroutine at the start of your script. You can simply copy and paste it into your script or you can place it in a text file and include it in your script using the include statement.
SUB WildcardDropTables (vExpression)
// Loop through the tables within the model
FOR i = 0 TO noOfTables()-1 STEP 1
// Get the current table name
LET vCurrTable = tablename(i); // Get the current table name
// If the table name matches the pattern then drop it
IF wildmatch('$(vCurrTable)','$(vExpression)') THEN
DROP Table [$(vCurrTable)];
LET i = i - 1; // Needed as table index reduces once table is dropped
END IF
NEXT
// Clear the variables so they don't persist
LET vExpression = null();
LET vCurrTable = null();
END SUB
I've included some comments for those that want to know how it works so I won't bother trying to explain it here.
With the subroutine defined you can call it at any point after and as many times as you like. Calling it is simple as follows:
CALL WildcardDropTables ('*-temp')
If you want to know what is valid to use within the passed pattern, look up the wildmatch() function in the QlikView help.
Now before the best practice police hang me from the rafters, it is indeed best practice to
drop a temporary
table as soon as is possible within the script to free up the memory it
is using. But it's a nice trick and there are always situations where best practice can't be applied.
I've a similar subroutine to drop temporary fields which I'll share soon.
