Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am new to QlikView and find it difficult to understand the variables especially the $ expansion. I have written the below simple script and found the result variaying. I was expecting the dates to be shown as meaning dates but the results are in decimal numbers. Please can some one explain why I am getting mixed results?
set aa = 'a';
set bb = '01/01/2001';
let cc = Date#('01/01/2001');
for i = 0 to 1
TestTable:
Load
'Testing' as Col1,
'$(aa)' as Col2,
Date#($(bb)) as Col3,
$(bb) as Col4,
Date#('01/01/2001') as Col5,
$(cc) as Col6,
Date#($(cc)) as Col7
autogenerate i;
next i;
Exit script;
Many Thanks,
Taj.
Hello Taj,
Very basically, what your functions are doing is evaluating the content of the variable when you call them with dollar expansion. So it must be the result of the mathematical operation 1 / 1 / 2001. From there the decimal numbers.
QlikView doesn't have datatypes, and any value is subject to two interpretations: numeric or literal (string), and thus represented as money, num, date, timestamp...
Check the following code. Quoting helps to keep the value as a literal (string in programming languages)
SET vDateSET = '01/01/2001';
LET vDateLET = '01/01/2001';
For i = 0 to 1
Load '$(vDateSET)' AS DateSET, // this will return the value stored in the variable as is
Date('$(vDateSET)') AS DateSETasDate, // this will return a numeric value, although represented as a date
$(vDateLET) AS DateLET, // this evaluates 1/1 = 1 / 2010 = 0,0004997
Date($(vDateLET)) AS DateLETasDate // this returns a numeric, although represented as a date (day 0 for QlikView is 31/12/1899
AutoGenerate 1;
Next
Hope that helps.
BI Consultant