Learn 2 tricks today in Qlik Scripting: Creating an empty table, throwing an error with a message
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:
When you load data in a loop, for example
FOR EACH vFile in FileList('lib://AnyLib/*.qvd')
// your load logic ...
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:
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.
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();
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:
May all your Qlik Scripts work flawlessy!
This is the first of a blog series around Qlik's SaaS vision and execution.
Unfortunately up to today, there is no show-condition in any Qlik Sense chart other than the pivot table and straight table, but with this trick you can dynamically show / hide certain measures in any chart.