WHEN and UNLESS

By Barry Harmsen

Mr. ComplainypantsNo, this post isn’t about that negative co-worker or friend who’s always second-guessing your ideas and solutions, it’s about two QlikView script statements that I believe deserve a little more attention: WHEN and UNLESS. (Inspired by Steve Dark’s post on Hidden QlikView Features)

Both statements can be used as an easier, shorter version of the IF-THEN-END IF statement. For example, to set the default value for a variable, but only if it hasn’t been set already:

WHEN ‘$(Variable)’ = ” SET Variable = ‘Value’;

Or, another example, when you’re in a loop and want to add a separator to a concatenated value, unless it’s the last iteration of the loop:

UNLESS $(i) = $(ForMax) LET Variable = ‘$(Variable);’;

One thing you’ll immediately notice when you use these statements is that QlikView syntax highlighting doesn’t seem to recognize them. Don’t worry though, as the attached example application demonstrates, they do really work.

Download the example application

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past 10 years I have been specializing in Qlik and a more user-centric form of BI. I have done numerous QlikView and Qlik Sense implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.

5 Comments

  • 1
    April 5, 2013 - 17:46 | Permalink

    Thanks very much for the mention Barry – enjoy the Bahamas!

  • 2
    Speros Kokenes
    April 11, 2013 - 14:33 | Permalink

    Hey Barry, it was nice meeting you in the Bahamas.

    This is a great post. I had no idea this functionality existed. Out of curiosity, how did you discover this?

    • 3
      April 11, 2013 - 21:09 | Permalink

      Hi Speros,

      Good meeting you, and congratulations once again on winning the app contest, well deserved.

      The WHEN and UNLESS statements are actually in the help file. I just stumbled across them while looking for something else. Sometimes it pays off to RTFM 🙂

      Take care,
      Barry

  • 4
    Speros Kokenes
    April 12, 2013 - 14:35 | Permalink

    Thanks Barry.

    Great, so I can expect it to be supported by QlikTech. Good to know.

    Speros

  • 5
    July 1, 2013 - 04:53 | Permalink

    Nice post, Barry! There is another compact statement that I have found very useful for looping.

    It is WHILE along with recno() .

    For example, to generate distinct values, you can use this

    LOAD date(fieldvalue(‘FieldName’,iterno())) as FieldName
    AUTOGENERATE 1
    WHILE len(fieldvalue(‘FieldName’,iterno()));

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    Masters Summit for QlikView

    I'm proud to be one of the speakers at the Masters Summit for QlikView, which will be held at the...

    Close