Thursday 26 June 2014

QlikView Funtions: today() and now()

In this post I want to take a look at two very closely related functions, today() and now(). Many of the more advanced calculations that we want to perform with dates and times require us to know what date it is, and what time it is.

First things first, let us take a look at what the help says about these two functions:

today([timer_mode] )
Returns the current date from the system clock. The timer_mode may have the following values:
0 Date at script run
1 Date at function call
2 Date when the document was opened
Default timer_mode is 2. The timer_mode = 1 should be used with caution, since it polls the operating system every second and hence could slow down the system.

now([timer_mode] )
Returns a timestamp of the current time from the system clock. The timer_mode may have the following values:
0 Time at previously finished reload (not currently ongoing reload)
1 Time at function call
2 Time when the document was opened
Default timer_mode is 1. The timer_mode = 1 should be used with caution, since it polls the operating system every second and hence could slow down the system.

Monday 23 June 2014

Review of QlikView for Developers Cookbook

Packt Publishing have been slowly but surely securing themselves as the one stop publishing house for QlikView books. With no less than 6 titles now available and more in the pipeline, there can be no doubt they've been busy, but are they worth spending your hard earned cash on?

Some time ago I reviewed their first QlikView book QlikView 11 for Developers which was written by Miguel Garcia and Barry Harmsen and released back in November 2012. The subject of this review is the next book released, QlikView for Developers Cookbook written by Stephen Redmond. I confess I've had this book since it's release, but with a baby QlikView addict at home and endless work commitments, time really hasn't been on my side lately. And as with my previous book reviews, I like to ensure I've not just skimmed through the book but given it a thorough read. There isn't much point in me spouting opinion, and I certainly wouldn't recommend a book unless I honestly knew it's contents in detail. So here goes...

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  

    // Clear the variables so they don't persist
    LET vExpression = null();
    LET vCurrTable = null();


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.