My Favourite little known Qlik Script tricks

There are not too many people who learn Qlik Scripting in depth these days, as you can do a lot with the Qlik Sense Data Manager already. However, I am one of the old sort of Qlik Scripters, who learned it way before Qlik Sense. And I want to share a few goodies with you:

Creating an emtpy table:

When you load data in a loop, for example

FOR EACH vFile in FileList('lib://AnyLib/*.qvd')
// your load logic ...
NEXT vFile
then you should load the first table with
[tableName]: LOAD ....
and the subsequent tables with
CONCATENATE([tableName]) LOAD ....

(note the different prefixes). You may be lucky when all files, that you are going to load, have the identical number and names of columns, then Qlik will automatically concatenate the files into one table. But if you want to be certain, create the table before with 0 rows and at least one field of the expected columns:

[tableName]:
LOAD * INLINE [CreationDate, CustomerID, CustomerName];
This will create an empty table with 3 fields. Now only use
CONCATENATE([tableName]) LOAD .... in your loop, that's it.Throw an error and stop script

Qlik Script may break in execution due to an error here and then, and typically you don't want this to happen. But what if a certain logic requests you to break. I am missing a THROW command or something ... But you can do this: Write just what you want to say in square brackets (so Qlik treats that text between like one expression). E.g.

LET vErrorMsg = 'this shouldn''t have happened, error is ' & e();
[Error: $(vErrorMsg)];

Qlik Script will break at that line and print the following to the reload log:The following error occurred:

Unknown statement: [Error: this shouldn't have happened, error is 2.718281828459]

This is exactly what you want in certain cases, stop and give a meaningful message back. Note that the message which may calculated text by using $(variables) like in above example.

One case, that saved my life a couple of times, is to check that the number of rows of a table have not increased after i left-joined another table, from which I believed it is 1:1 or 1:0 related, but not 1:N related:

LET vRowsBefore = NoOfRows('mainTable');
LEFT JOIN (mainTable) LOAD * RESIDENT foreignTable;
IF NoOfRows('mainTable') > vRowsBefore THEN
LET vErrorMsg = 'LEFT JOIN exploded table "mainTable" from $(vRowsBefore) to ' & NoOfRows('mainTable');
[Error: $(vErrorMsg)];
END IF

May all your Qlik Scripts work flawlessy!