LET/SET date question

[#1]

Hi *,

I'm setting my "today date" via a variable.

LET v_today = date(date#('08/14/2009','MM/DD/YYYY'),'MM/DD/YYYY');

Further in the Script I'm doing some calculations based on the variable eg. $(v_today) - Datefield AS xyz

If I use the statement Let v_today... I'm getting : Error in expression ')' expected as soon as I use $(v_today).

If i use Set the calculation is done, although v_today is date(date#....

Can someone explain me why this happens?

Thanks.

[stephencredmond]

Hi,

Why are you using the date and date# functions? The date# function by itself will parse the text into the date format that you want.

This statement works very well to assign the variable (and if you look in your document, in the variable overview, you will see the value assigned). I imagine that the error about the ')' is unrelated to the assignment but is related to your use of the value. Instead of $(v_today), you may need to use '$(v_today)'.

Stephen

[#3]

Hi,

(also check Stephen's answer for a solution to your problem; just explaining what's happening exactly here)

The difference between 'set' and 'let' is that 'set' just assigns the text behind the '=' (i.e. the entire expression) to your variable - that's why it contains 'date(date#(...))' later; 'let' evaluates the expression, and assigns the result to your variable.

When you use $(v_today) in the script, QlikView will evaluate the contents to some text value, put that in place of the variable ( $(v_today) ), and then run the resulting script.

If your variable contains a literal date value, this will be added, resulting in something like

LOAD
...
08/14/2009 as date_field
...

which is interpreted (literally, but incorrectly) as some script calculation. For this case, Stephen suggests putting quotes around the $(v_today), so it gets interpreted as a piece of text instead of some script calculation.

If your variable contains the expression text (what happens in the 'set' statement), you'll end up (after variable substitution) with something like

LOAD
...
date(date#('08/14/2009','MM/DD/YYYY'),'MM/DD/YYYY') as date_field,
...

which is correct script code.

Bottom line: in script, $(variable) will always be expanded to the exact (textual) value in the variable; make sure that the script is correct if you replace $(variable) with the exact value of the variable yourself.

Hope this clears things up a bit for you.

Martijn ter Schegget