Help with Variables

[#1]

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;

-----------Results---------

Many Thanks,

Taj.

[Miguel_Angel_Baeyens]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica