topic Re: Selecting data with a Date variable in QlikView

Selecting data with a Date variable

Mon, 19 Nov 2012 13:51:26 GMT

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.

Re: Selecting data with a Date variable

MayilVahanan — Mon, 19 Nov 2012 13:59:14 GMT

Try like this

Load * from filename.xls

where

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

Hope it helps

Re: Selecting data with a Date variable

Mon, 19 Nov 2012 14:23:09 GMT

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.

Re: Selecting data with a Date variable

Gysbert_Wassenaar — Mon, 19 Nov 2012 14:28:36 GMT

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.

Re: Selecting data with a Date variable

Mon, 19 Nov 2012 14:41:13 GMT

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

Field not found - <$vTodaysDate>.

What does the $ mean anyway?

Re: Selecting data with a Date variable

Gysbert_Wassenaar — Mon, 19 Nov 2012 14:44:43 GMT

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.

Re: Selecting data with a Date variable

Mon, 19 Nov 2012 14:48:12 GMT

Many thanks Gysbert!