Why LET vTodaysDate = Num(Today()) when comparing?

[#1]

Hi,

When doing a LOAD on a table using a WHERE on a date, like:

LOAD Date,

     Column2,

     Column3

FROM MySpreadsheet.xlsx

(ooxml, embedded labels, table is Sheet1);

WHERE MyDate = $(vTodaysDate)

Why do I have to set (LET) the variable using the NUM function?

LET vTodaysDate = Num(Today())

Why can't it just be vTodaysDay = Today()? Which I see doesnt work.

[swuehl · Accepted solution]

Have you tried enclosing your variable in single quotes to handle the date string correctly?

LET vTodaysDate = Today();

LOAD Date,

     Column2,

     Column3

FROM MySpreadsheet.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE MyDate = '$(vTodaysDate)';

(is MyDate another field in this Excel file or do you want to use in fact the Date field?)

[hic]

The Today() function is a dual function This means that it returns both a string and a number. Hence

   Let vTodaysDate = Text(Today());

will assign a string, whereas

   Let vTodaysDate = Num(Today());

will assign a number to the variable.

A comparison with a date (the where clause) requires that the other side of the equal sign can be interpreted as a number, e.g. a valid date format.

Stefan's solution works fine, since the single quotes force QlikView to treat the variable as a string and QlikView then tries to interpret the string and also succeeds, since the string contains a valid date format.

(see more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/13/dual).

HIC