Selecting data with a Date variable

[#1]

Hi guys,

New to Qlikview, and I've poured over posts and documentation and can't find the answer to what should be a very basic question.

I am loading a set of records from an excel spreadsheet, this is no problem. However, in the spreadsheet is a date column, and all I want to do is select records where the date falls into a simple range, and I want the dates to be variables.

So lets say I am :

LOAD MyDate,

  Colum2,

  Column3

FROM MySpreadsheet.xlsx

(ooxml, embedded labels, table is Sheet1)

But in my WHERE I want to have WHERE MyDate = vTodaysDate OR MyDate = vTodaysDateMinus1

Being that vTodaysDate = Today()

and vTodaysDateMinus1 = Today()-1

So before the LOAD I am setting:

LET vTodaysDate = Today();

LET vTodaysDateMinus1 = Today() -1;

I am leaving some stuff out here, but there is a reason why I want to use the variables versus just using Today() in the WHERE clause. Which does work btw. However, anytime I try to use a variable in its place, my Load returns nothing.

Thanks ahead of time for your help.

[MayilVahanan · Accepted solution]

HI

Try like this

Load * from filename.xls

where

match(MyDate,$(vTodaysDate), $(vTodaysDateMinus1));

Hope it helps

[#3]

Awesome this worked! Many thanks.

Follow up question though... Do I always have to use MATCH when comparing values in columns to values in variables? This is the first time I am seeing the MATCH function.

[Gysbert_Wassenaar]

No,  WHERE MyDate = $vTodaysDate OR MyDate = $vTodaysDateMinus1 should work as well. The match function is used where in sql you would use IN ('Val1','Val2'...). You can also use it if you use OR for several comparisons on the same field, as is the case in your where clause.

[#5]

Hi Gysbert, thanks, but when I try to do that, I get the error:

Field not found - <$vTodaysDate>.

What does the $ mean anyway?

[Gysbert_Wassenaar]

Oops, forgot some '(' and ')' -'s

WHERE MyDate = $(vTodaysDate) OR MyDate = $(vTodaysDateMinus1)

The $(variable) construction is called dollar expansion. See here for an explanation of the why and how.

[#7]

Many thanks Gysbert!