What makes a variable expression valid?

[prayner]

I am not sure what defines a valid expression for variables in the data load editor:

For example: 

INCORRECT:  Let vLastExecDate = max(date);

CORRECT: Let vLastExecDate = peek('date', 0, 'table_name'));

OR

INCORRECT: LET vIsS3TablePresent = NoOfRows('check_s3')>0;

INCORRECT: LET vIsS3TablePresent = if(NoOfRows('check_s3')>0, 1, 0);

What am I missing? This is an issue I face every month and I haven't pinned down what makes an expression valid for LET variable creation method.

[BenjaminT]

Can you explain what you mean by incorrect? are you getting a syntax error in the UI or an error when you try to run the script?

From first glance you have a second closing bracket here which should cause an error:
Let vLastExecDate = peek('date', 0, 'table_name'))

[rwunderlich · Accepted solution]

Your incorrect expressions are returning null. A variable with a null or empty value at the end script execution will not get copied to the UI, so it appears that the variable "isn't created". 

Max(date) returns null because Qlik doesn't know what "date" is. Peek('date', 0, 'table_name') "works" because you are fetching a specific value from a table. It is somewhat illogical to me because you are applying Max() to a single value. If you want to get the max value of the field named "date" into a variable it's a two step process -- a LOAD followed by a Peek():

LOAD Max(date) as Maxdate Resident table_name;
LET vLastExecDate = Peek('MaxDate');

This approach can be slow if you have a lot of rows in "table_name". A faster method that only reads the distinct values of date is:

LOAD Max(FieldValue('date', RecNo())) as MaxDate
AutoGenerate FieldValueCount('date');

NoOfRows('tablename') returns null if the table doesn't exist. (Admittedly it would be sweet if the documentation mentioned this).

A working expression to test for the existence of a table is:

LET vIsS3TablePresent = Alt(NoOfRows('check_s3'),-1)>-1;

If you need to test that the table exists and also has more than 0 rows then 

LET vIsS3TablePresent = Alt(NoOfRows('check_s3'),-1)>0;

Lots of scripting blog posts and downloadable examples on my website https://qlikviewcookbook.com/.

@ChristofSchwarz , aka "Dr Qlik-Script" is also blogging regularly about Qlik Scripting. Christof and I will be doing some joint presentation on Qlik Script tips at the Masters Summit for Qlik in September. Maybe we'll do a "tip-off".

-Rob