topic Why LET vTodaysDate = Num(Today()) when comparing? in QlikView

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

Mon, 19 Nov 2012 15:39:00 GMT

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.

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

swuehl — Mon, 19 Nov 2012 16:05:29 GMT

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?)

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

hic — Mon, 19 Nov 2012 16:52:31 GMT

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