Dates in variables

[Anonymous]

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

[Anonymous · Accepted solution]

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

[hic]

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

[Anonymous]

Thanks a lot, that worked!

[Anonymous]

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.