topic Date format changing between variable declaration and usage in QlikView

Date format changing between variable declaration and usage

Mon, 17 Mar 2014 15:25:49 GMT

Hi,

I'm using variables within my load script to specify a max and min date range to load data for.

I am declaring the following in the variables:

let vFirstDate = date(YearStart(addYears(today(), -2)), 'YYYY-MM-DD');

let vLastDate = date(today()-1, 'YYYY-MM-DD');

When running through debug I can see that vFirstDate is set to 2012-01-01 and vLastDate is 2014-03-16

All good so far.

The next step of the script is to load some data in from the DB, and use these min/max values against a specific date. The line in my script is:

BDT.servicedate between $(vFirstDate) and $(vLastDate)

When the debugger interprets that line however, I get the following SQL:

BDT.servicedate between 2012-01-01 and 16/03/2014

This then fails, as the field on the DB is expected YYYY-MM-DD.

Any ideas?

Thanks!

Rory.

Re: Date format changing between variable declaration and usage

ThornOfCrowns — Mon, 17 Mar 2014 15:51:07 GMT

I normally, for a 'belt and braces' approach, add a CHR(39) to the start and end of the variable, so the line looks like:

between '2012-01-01' and '2014-03-16'

as I find that otherwise it tends to fall over / return no results.

Re: Date format changing between variable declaration and usage

Mon, 17 Mar 2014 15:54:48 GMT

Hey Rory,

Can you try BDT.servicedate between '$(vFirstDate)' and '$(vLastDate)'

Re: Date format changing between variable declaration and usage

Mon, 17 Mar 2014 15:58:26 GMT

Hi All,

Thanks for the suggestions... turns out it was a spelling error in my script. Total noob mistake.

Does highlight that it would be nice for the script editor to have some intelligence to let you know if you're trying to use a variable that has not previously been declared, but still a silly mistake on my part.

Cheers,

Rory.