Wednesday 4 June 2014

Dropping Tables using a Wildcard


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.

4 comments:

  1. I personally would not do this for a few reasons. 1. Releasing RAM as you can (you brought up). 2.) Generally you are adding more code than you would be saving. 3.) It is a lazy way of handling temp tables which "absolves" developer from thinking through their logic as they build up their data model.

    But don't get me wrong. It is extremely clever and worth posting :)

    ReplyDelete
  2. Thinking about this some more, another use might be if we have a qvd generator that creates several qvds, but we want to empty it out after each run except for maybe a stats table. We could add a "not" to the wildmatch function and then use our stats table name as the expression. That might be very usable.

    ReplyDelete
  3. Hi Aaron
    As I said in the post, I certainly wouldn't consider it best practice or to be used in every situation but there are always cases when best practice can't be applied. And I have a saying "Best practice is only best if it offers a benefit".

    I can think of a few uses for this method, such as in a PoC where time is a big constraint and just being able to drop all temp tables in one line could save you 10 mins of making sure you've listed them all (a bit like using qualify, great for PoC but not so great in a live app). Or maybe dropping large parts of a data model after a binary load.

    Regards
    Matt

    ReplyDelete
  4. I like the general idea of a subroutine that can do things to tables based on the table name. Dropping the tables is one thing. Running a STORE command for each table is another possibility.

    Thanks for the blog, Matt.

    ReplyDelete