Wednesday 25 December 2013

Merry Christmas

Just a quick post to wish you all a very merry Christmas. I shall be enjoying our first Christmas with our new junior QlikView addict who it seems has been spoilt rotten.

I've been somewhat lax at posting recently but I'll do my best to get back to writing some more. I have a long list of ideas for new posts.


Tuesday 12 November 2013

QlikView Notepad++ Language Definition v2.2

I've just released a new version of the QlikView Notepad++ Language Definition, mainly to correct an issue with the function list support not working with the latest release of Notepad++ (version 6.5 onwards). This release also contains the following additional fixes/functionality:
  • Corrected a minor issue with the DISTINCT keyword not being highlighted in some situations.
  • Added a check to ensure line comments terminate at an End Of Line (EOL) character.
  • Corrected issue with operator identification. Although operators are not highlighted in QlikView, correct identification prevents incorrect highlighting of keywords and functions when an operator buts up against it. 
  • Added additional operators to fix some minor issues with incorrect highlighting of keywords.

As always, head over to the Notepad++ Language Definition page for the download link and instructions.

Thanks go to Bruno Santos for pointing out the function list issue and testing the fix!

The new version of Notepad++ also has auto-completion turned off by default. You can turn it back on quite easily though:
  1. Select Settings -> Preferences... from the menu bar. 
  2. Select Auto-Completion from the list on the left and then ensure the options are set as follows:

The options under Auto-Insert are optional and enable auto insertion of closing brackets and quotes are automatically entered when an opening bracket or quote is typed.

Monday 14 October 2013

Half price Packt books until Thursday

The guys and gals over at Packt Publishing are having a half price sale on all ebooks and videos until Thursday 17th October 2013. That means you can grab the great QlikView 11 for Developers and the recently released QlikView for Developers Cookbook as ebooks for 50% off the listed prices.

There are no catches or twists, all you have to do is head on over to the Packt website and enter the discount code COL50 at the checkout. Simples!

Monday 8 July 2013

QlikView Notepad++ Language Definition v2.1

Notepad++ version 6.4.1 was released recently and contains some exciting new additions.Top of the list is the new function list which has been awaited for a while now and is a popular feature in many other code editors (including UltraEdit).

This is another significant update and includes:
  • New function list support allowing Notepad++ to show subroutines, qualify and unqaulify statements, table loads, info loads, mapping loads and store statements all identified within the function list. 
  • Removed some functions from the auto-complete file as they are only valid in expressions not the script. 
  • Changed priority of highlighting to fixed issue with some keywords not highlighting correctly 
  • Fixed some minor issues with keywords being highlighted when they were shown in the wrong positions. 
  • Added tool tips for some additional functions.
This version has the following known limitiations:
  • The if() function cannot be differentiated from the IF...ENDIF statement and thus will be shown as a collapsible block with an incorrect end point.
  • Tool tips are not available for all functions. Remaining functions to be added in a future release.
As usual, head on over to the QlikView Notepad++ page to grab the download and instructions of how to update. If you find any issues, leave me a comment below and I'll try resolve them in a future release.

Tuesday 25 June 2013

QlikView for Developers Cookbook

Stephen Redmond (from the well known has been busy writing a sequel to QlikView 11 for Developers which I reviewed a couple of months ago. Unlike it's predecessor, QlikView for Developers Cookbook looks at a serious of "recipes" for solving well known QlikView problems.

Coming from Stephen you can guarantee some advance QlikView content and great advise. I'm eagerly awaiting my copy for review.

You can grab your own copy now from

The UltraEdit Function List with QlikView Script Files

Since the release of version 1.0 of the QlikView wordfile for UltraEdit I've had a few people ask me what the UltraEdit function list support offers and how to use it, so here goes.... 

It might seem obvious but you need to have installed the QlikView wordfile before the functionality described below will work. If you haven't already, details of how to do so can be found on the QlikView for UltraEdit page.

The function list can be shown in UltraEdit by either going to "View -> Views/Lists -> Function List" on the menu bar or by pressing F8. By default the function list is shown on the right hand side of the screen but can be dragged and docked to just about anywhere you want it to sit.

Friday 31 May 2013

QlikView UltraEdit Wordfile v1.0

Hot on the heals of the QlikView Notepad++ Language Definition v2.0 comes the release of the Wordfile version for those who prefer to use UltraEdit instead. Like the Notepad++ version, this is a big update from the previous version (v0.2) and adds the following:
    • Improved code section folding
    • Added missing "GROUP BY" keyword
    • Auto-completion of all keywords and functions
    • Subroutines and LOAD statements supported in the UE function list
    • Other performance improvements
    Head on over to the QlikView UltraEdit page to grab the download and instructions of how to update.

    Thursday 30 May 2013

    The Most Useless QlikView Feature

    Yup, even QlikView has one and for me its not a difficult choice....Animated Charts.

    In the endless number production QlikView solutions I've seen over the last 8 years, I've never once seen an animated chart used and that speaks volumes, but before anyone gets all agitated, breath for a second and let me try and explain why I dislike them so much. Lets take the below simple bar chart as an example:

    Sunday 26 May 2013

    Review of QlikView 11 for Developers

    There have been a handful of books on QlikView released over the last couple of years, most set on trying to teach new users how to build successful applications, but all have yet to hit the spot for me; there was always something missing. I regularly get asked by customers if there is a book I would recommend, but for me to recommend a book to a client I need to be confident in the advice it gives. Ultimately I'll be the one who ends up picking up the pieces if it's bad advice. Because of this, in the past, I've avoided recommending a book and instead suggested other routes and resources available.

    QlikView 11 for Developers was released towards the end of 2012 and is authored by Miguel Garcia and Barry Harmsen, both experienced QlikView consultants. The back of the book makes the following claim:
    This book will help you learn QlikView Development from a basic to a practitioner level using a step-by-step approach in a practical environment, and apply proven best practices along the way.

    Thursday 16 May 2013

    QlikView Notepad++ Language Definition v2.0

    OK so after the teaser Tweets over the last couple of days, it's finally here, the new update to the QlikView Notepad++ Language Definition. This is the biggest update yet adding lots of new functionality including:
    • Improved code section folding
    • Improvements to keyword combinations to prevent incorrect highlighting (eg. INFO should not be highlighted unless it is followed by LOAD of SQL)
    • Added missing "GROUP BY" keyword
    • Number formats support hex even though no formatting is shown for numbers within QlikView by default (prevents correct highlighting if appears in a string).
    • Auto-completion of all keyworods and functions 
    • Tool tips shown for most commonly used functions (including colour, string, aggregation, numeric, inter-record and many other functions).
    Rather than me babbling on about it anymore, head on over to the QlikView Notepad++ page to grab the download and instructions of how to update.

    Thursday 9 May 2013

    User friendly field names in QlikView

    I spent a day last week working with a customer planning some improvements to their solution and taught them a few little tricks on the way. One tip that came up in conversation happened to be one of my favourite little QlikView tricks (yes I know I need to get out more often) and so I thought I'd share it here too.

    Having "clean", user friendly field names is a key aspect of any polished QlikView solution. Even if you disable your users ability to create their own custom objects in the browser, they can still see the often unfathomable field names within the underlying data model when they view their current selections. Even if you don't put a current selections object in your application, it is available as a floating window on the toolbar for both the IE plugin and the AJAX zero footprint so users will inevitably see them at some point.

    To solve this problem, you could boviously spend many hours working your way through your script(s) renaming all the fields using AS like this:

        CusID AS [Customer ID],
        CusName AS [Customer Name],
        CusCouCode AS [Customer Country Code],

    This would of course work and is a perfectly valid approach, but it can be complicated when your scripts become complex. For example, renaming a field in  table which is then the source of a later resident load will mean you have to cascade the new field name throughout everywhere it is used later in the script. There has to be a better, faster way right? Thankfully there is, although don't get too excited, it's no miracle and you'll still have to type in all the user friendly field names. QlikView is good, but it can't read your mind!

    Sunday 17 March 2013

    Dates in QlikView - Part 3

    It's taken me a little longer than I wanted to complete this next instalment of my series of posts on dates in QlikView. I wanted to get the post about the dual() function finished first as it can be very useful when working with dates and is referenced below. My work commitments keep getting in the way too. Anyway, let's crack on...

    In the first 2 parts of this series, I looked at how dates are stored in QlikView and how to ensure you have them in the right format to be most effective. So now that we have that out the way, lets looks at how we can start to make dates more useful for using in charts and objects within the front end of QlikView.

    Despite looking in part 2 at how we worked to combine parts of a date stored separately in order to create a "true date" from them, we often want to do the reverse also and split the date into its composite parts. For example, we might want to be able to select individual years without having to select every date in those years, or we might wish to be able to display a bar chart showing our sales by month.

    Thursday 7 February 2013

    QlikView with Leap Motion Controller

    A QlikView friend of mine Barry Harmsen posted an interesting video last week of his first play with controlling QlikView using the Leap Motion Controller. For those that aren't aware of the Leap Motion, it's a revolutionary new interface for close range gesture control. They aren't available for sale yet but a small number of units have been released to developers and Barry was lucky enough to get his hands on one.

    I'm sulking a little as my test unit has yet to turn up but I'm assured it's on its way. Obviously this has a little way to go before it becomes a robust and fast method of interacting with QlikView but it's an interesting taste of what we can hopefully look forward to in the future.

    Tuesday 22 January 2013

    QViewer QlikView Debugging Trick

    Dmitry, who developed QViewer, posted a great little trick for seeing the contents of a resident table whilst debugging your script in QlikView. You can view his original post here but I thought I'd give you a quick step-by-step of how to make it work.

    The first thing you need to do is to download and install QViewer if you haven't already. If you don't know what QViewer is, check out my earlier review post. You an download it from the website and best of all there is a free version. The only limitation is that it will load only the first 10,000 rows from the QVD file. This is normally more than enough for most applications and especially when debugging. If you use QViewer a lot then the full version removes this limitation for $45 (USD) (and will also help to encourage Dmitry to continue his great work no doubt).

    You will be calling QViewer from within your QlikView script and so you need to change the privileges of your QlikView script to allow it to execute external programs. By default this is prohibited. In the QlikView Script Editor, select the Settings tab at the bottom left and ensure the "Can Execute External Programs" check box is ticked.

    Tuesday 8 January 2013

    QlikView 11 for Developers

    I'm currently awaiting a hard copy of QlikView 11 for Developers but until it arrives I've been skimming through the e-book. A full review will follow when I've digested it properly but so far I'm quite impressed.

    For those new to QlikVew it offers a step-by-step guide to building your first QlikView application and includes many of the most common obstacles and their solutions. And for those who've been working with QV for a while I guarantee you'll still pick up some useful tips and tricks along the way such as best practice and dashboard design.