Tag Archives: script

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 »

Hands-on with QlikView Script Generator

A marble track of data!I have a confession to make. As someone who comes from a background of visual ETL tools (such as SAP BO Data Integrator and Informatica PowerCenter) loading data into QlikView via scripts has always struck me as somewhat old-fashioned.

Don’t get me wrong. I can very much appreciate a good QlikView script. It is just that whenever I hear someone say that QlikView does not need a visual way to load data and that script is fine it reminds me of the “veteran” DBA’s I’ve met during my work as a BI Consultant. If you have worked in BI/data warehousing for any length of time you probably know the type, lots of facial hair, heavy smoker, followed his last training in 1986 and, most importantly, he is absolutely adamant that PL/SQL is the only right way to do ETL. Graphical tools are for kids and amateurs!

I do not agree with that statement, so you can imagine that I was pleasantly surprised when I read the announcement for QlikView Script Generator (QsGen) this week. QsGen promises, amongst other things:

  • Visually design complex scripts to load your data
  • No more need of scripting skills
  • 500% faster design

Interested to see if I really would not need any scripting skills to build fantastic “marble tracks” of data I decided to download the trial version and test it with QlikView 10 SR1.

read more »

Merging tables: concatenation

Merging tables using the concatenate and join prefixesOne of the subjects I often see new QlikView developers struggle with, especially those without prior database experience, is merging data from different tables. In this series of two posts I will explain how to use the CONCATENATE and JOIN prefixes to bring together data from multiple tables.

Simply put, the CONCATENATE prefix lets you add rows to a previously loaded table, while the JOIN prefix lets you add columns to a previously loaded table.

Read on for the first post, which covers the concatenation of tables in more detail. read more »