Getting Started with Qlik Sense

Yer yer I know, its about time I put together my first post about Qlik Sense. Better late than never hey. As a starting point, I thought I'd post the below video "Qlik Sense Desktop - quick build tutorial" from Qlik. It is the best "get started guide" I've found so far. It walks you step-by-step through getting Qlik Sense installed to creating your first visualisations and answering business questions. And you don't need to be a coding whiz either, not a single line of script was hurt during the making of this video.

QViewer v2 Released

Some time ago now I reviewed QViewer, a great little tool that allows you to view the contents of a QVD without having to load it into a QlikView app. This can be a really useful thing to do when working with complex ETL solutions in QlikView. Since then it has become a firm favourite in my QlikView development toolbox.

Dmitry originally created QViewer as a tool for himself, only later releasing it to other QlikView developers. Because of it's humble origins, it was never the most polished of software tools, but the fact that it has been downloaded more than 10,000 times speaks for itself.

This week Dmitry has released a v2 of QViewer. As well as a new, cleaner user interface, the engine has been rewritten to give huge performance improvements. And best of all, if you already have a license for QViewer then you can download v2 for free here http://easyqlik.com/download.html.

Grab yourself some cheap QlikView ebooks

The guys over at Packt Publishing are marking their 10th birthday with a sale. You can grab any ebook or educational video on their site including their full array of 6 QlikView books for just $10 USD (about £5.90 GBP) each. Click the banner below to find out more.

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.

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...

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.

Theft

It saddens me greatly that I'm having to write this post, but here goes...

From time-to-time I receive requests from other websites and authors for permission to reproduce parts of my blog posts. I am normally very flattered that people consider what I write is good enough to reference or reproduce, and until today, I have been more than happy to give approval to such requests. The only thing I have ever asked is that they make it clear what material they have reproduced and to cite the source when doing so.

Unfortunately, I have today come across another blog which has copied images and entire posts word-for-word from QlikViewAddict.com. After taking some legal advice I have sent  the owner of the site a copyright infringement notice. This time I am far from flattered!!!

I am also saddened to say that I recognise material from other prominent QlikView blogs too and so if you are the author of a QV blog, please drop me a message so I can send a link to the offending site. I refuse to advertise it here.

For anyone that wants to use any material from this site, unless otherwise stated, I am the sole author and copyright owner of all material published on QlikViewAddict.com and I politely request that you contact me and ask before doing so. You'll find a big "Contact Me" button at the top of every page of the site and I don't bite (well only at the weekends). I receive a lot of messages via this site and I try to reply to as many as I can. Unfortunately, due to having a family and paying customers to keep happy, I don't get to answer them all but I do my best.