Tag Archives: script

Bitmetric Qlik Sense Coding Conventions – free download

At Bitmetric, we don’t believe in universal ‘best practices’. Which approach, standard or method works best should always be evaluated in the context of the environment. That being said, there are certainly starting points that can serve as ‘good ideas’.

Today we offer you one such starting point, the Bitmetric Coding Conventions for Qlik Sense.

Read on and find out how you can get your free copy of this document.

read more »

Auto AutoNumber your key fields

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 »

Aliases, to indent or not?

“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 »

Scripts and wizards, too much script, not enough wizards

ScriptVsWizardsI 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 »

Masters Summit for QlikView: European Edition

Masters Summit for QlikViewAfter the success of the Las Vegas edition last April, I’m excited to be once again presenting alongside Rob Wunderlich, Bill Lay and Oleg Troyansky at the European edition of the Masters Summit for QlikView.

Masters Summit for QlikView; London and BarcelonaComing to London from October 9  to 11 and to Barcelona from 14 to 16 October, the Masters Summit brings you 3 days of hands on sessions where we will discuss advanced techniques in building complex solutions with QlikView. The goal of this event is to take your QlikView skills to the next level and help you become a QlikView  master.

For the early birds, there is an attractive discount of US$ 300 (around 225 Euro’s) until August 16th, which, for example, should be enough to cover air fare from most locations within Europe. Make sure you do not miss out on this great offer and:

Register for the Masters Summit for QlikView

I hope to see you all there!

DISTINCT can be deceiving

DISTINCT can be deceiving

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:

Regular LOAD DISTINCT

However, when used in combination with the JOIN or CONCATENATE statement, the results can be surprising, and even counterintuitive.

read more »

WHEN and UNLESS

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

Masters Summit for QlikView

Masters Summit for QlikView, April 16 - 18, Four Seasons, Las VegasI’m proud to be one of the speakers at the Masters Summit for QlikView, which will be held at the Four Seasons in Las Vegas on April 16 – 18.

In 3 days of hands-on sessions, Rob Wunderlich, Bill Lay, Oleg Troyansky and I hope to provide you with new knowledge and skills that will take your QlikView experience to the next level. We’ll be covering topics around advanced scripting, data modeling, expressions, visualization and much more. Besides an opportunity to invest in new knowledge and skills, this is also an excellent opportunity to network and exchange new ideas with your peers.

I hope you will be able to join us there. For more information, see the conference website.

Merging tables: the JOIN prefix

Merging tables: the JOIN prefixThis 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 »