No, 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
5 Comments
Thanks very much for the mention Barry – enjoy the Bahamas!
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?
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
Thanks Barry.
Great, so I can expect it to be supported by QlikTech. Good to know.
Speros
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()));