September 27, 2019 – 16:31
A quick note on a cool feature that I recently learned from Oleg Troyansky: AutoNumber.
I hear you thinking: “What? You didn’t know about AutoNumber? It’s been around for years. Hand over your Luminary badge!“. Admittedly, this is also what I thought when I hear Oleg mention it but it turned out that I misunderstood. Oleg wasn’t talking about the AutoNumber() script function, but the AutoNumber script statement. This statement was added in the June 2018 release of Qlik Sense (as well as QlikView 12.20) and lets you AutoNumber multiple fields, across multiple tables in a single line of script. And no, despite it being available for over a year, I hadn’t heard about it before. I’ll certainly be using it going forward though!
read more »
“Code is read more often than it is written”
– common programming adage
As someone who often has to look at/modify QlikView script written by others, I have come to appreciate the value of readable script. Rather than taking an “if it works, it works” attitude, I always try to write my script with readability and consistency in mind. Somewhere down the line someone else (or worse, you) might have to decipher what a specific script is doing in order to modify or extend it.
Today I want to ask your opinion on the readability of a specific part of the QlikView script, indentation of aliases within LOAD statements. To this end, I’ve taken a piece of script from the QlikView System Monitor and formatted the aliases in three different ways. (note: I don’t agree with the absence of white space in functions and between operators, but have left that as is)
Have a look at the three options, and then please answer the two multiple choice questions below.
read more »
I recently read an interesting post by James Richardson over at the Business Discovery Blog: Wizards vs Scripts. In the post James makes the case that QlikView scripting is not old-fashioned or too hard, but is evidence of the power of QlikView as a platform.
Let me first state that I love QlikView scripting. I’m a guy who writes script for fun. I also agree that scripting offers much more flexibility than a visual solution ever could. With those things in mind, I would like to present a different viewpoint: I think that QlikView places too much emphasis on scripting. In my opinion, the default approach should be much more visual.
read more »
Never think you know it all! Today I have for you a little ‘gotcha!’ about the LOAD DISTINCT statement that I picked up from Oleg Troyansky at the Masters Summit for QlikView.
Common knowledge about LOAD DISTINCT is that it will remove duplicate records from the input table, similar to how the DISTINCT clause works in SQL. For example:
However, when used in combination with the JOIN or CONCATENATE statement, the results can be surprising, and even counterintuitive.
read more »
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
September 21, 2011 – 12:30
Today I will show you how to store variables and their definitions in an external file… and why you would want to do that.
To start with the last part, in my opinion it is important to apply calculations and business rules consistently across QlikView applications. Doing this manually takes a lot of time and is prone to errors. Besides that, it is just not a very fun task. Fortunately, a lot of this can be handled by setting up a multi tiered environment with one or more QVD layers (for more information on this, see Rob Wunderlich’s excellent “Understanding & Best Practices with QVD files” webinar and slides).
Sometimes however, you need to use calculations that cannot be (easily) pushed to a QVD layer, for example when calculating averages or using set analysis. How can you store those calculations in a central place so they can be reused?
read more »
This is the second post in a series on merging tables in QlikView. In the previous post I wrote about appending rows to tables by using the CONCATENATE prefix, this time I will show you how to add columns to a previously loaded table by using the JOIN prefix. read more »