topic Re: Dates in variables in QlikView

Dates in variables

Anonymous — Wed, 21 Nov 2012 15:47:45 GMT

Hello! I´m trying to get a date in a variable, that I can use in a Where statement in the script. Here´s the closest I got, but for some reason the variable gets the value '1971' as soon as I use the date() function. In the Variable overview the value seems to be '2012-11-30' but in the Where statement it has the value '1971'. (I´m using version 9.7646-9)

Set vMonthAddition = 0;

For i = 1 to 6

Let vDate = date(MonthEnd(addMonths(date(Today()-2,'YYYY-MM-DD'),$(vMonthAddition))),'YYYY-MM-DD');

Data:

LOAD

            FROM X.qvd (qvd)
            Where FieldValidFrom <= $(vDate) AND FieldValidTo >= $(vDate)
            ;

Store Data into $(vDate).qvd;

Let vMonthAddition = $(vMonthAddition) + 1;

next
;

//Johan

Re: Dates in variables

Anonymous — Wed, 21 Nov 2012 18:22:49 GMT

Johan,

You have to use single quotes in the script when useing date variable:

Where FieldValidFrom <= '$(vDate)' AND FieldValidTo >= '$(vDate)'

Otherwise it calculates 2012 - 11 - 30 = 1971.

Regards,

Michael

Re: Dates in variables

hic — Wed, 21 Nov 2012 18:47:45 GMT

Michaels suggestion will work if the date format is recognized by QlikView. (Which usually is the case)

An alternative - that always works - is to create the variable not as a string, but as a number:

Let vDate = Num(MonthEnd(addMonths(Today()-2,$(vMonthAddition) )));

Date calculations are always made on the number, so you don't need any formatting functions inside the expression.

Then you can have a where clause without the quotes:

... Where FieldValidFrom <= $(#vDate) AND FieldValidTo >= $(#vDate)

HIC

Re: Dates in variables

Anonymous — Wed, 21 Nov 2012 20:54:39 GMT

Thanks a lot, that worked!

Re: Dates in variables

Anonymous — Wed, 21 Nov 2012 20:57:23 GMT

Thaks, this also worked, but I´ll use Michaels solution in this case, since I also want to store the table into qvd files, with the date as filename.