topic Re: Date comparison in LOAD not working in QlikView

Date comparison in LOAD not working

Thu, 31 Jan 2013 15:16:23 GMT

Date comparison in LOAD predicate does not seem to work. My date format is defined as "YYYY-MM-DD".

In Debug my variables show:

v30prior = 2012-12-29
v90prior = 2012-10-29

Yet, both of these loads return the same aggregation off all data from the resident table, not just an aggregate based on their where predicate.

NET30_DELTA:

LOAD
(SUM(AVERAGE_DELTA)/COUNT(AVERAGE_DELTA)) AS AVG_GROWTH30
RESIDENT DISC_USAGE
WHERE DISC_DATE >= $(v30prior);
LET vAVG_GROWTH30 = peek('AVG_GROWTH30');
DROP Table NET30_DELTA;

NET90_DELTA:
LOAD
(SUM(AVERAGE_DELTA)/COUNT(AVERAGE_DELTA)) AS AVG_GROWTH90
RESIDENT DISC_USAGE
WHERE DISC_DATE >= $(v90prior);
LET vAVG_GROWTH90 = peek('AVG_GROWTH90');
DROP Table NET90_DELTA;

I have confirmed that the dates in the resident table are indeed in the same format as the variables.

Also, I have tried using various combinations of DATE and NUM functions to no avail.

Hope someone can point me in the right direction.

Thanks,

Lew

Re: Date comparison in LOAD not working

Gysbert_Wassenaar — Thu, 31 Jan 2013 15:24:18 GMT

$(v30prior) might be evaluated to 2012 minus 12 minus 29 = 1971. Or it could be a text string so comparing it with a numeric date doesn't do what you expect. Make sure DISC_DATE and your variables all contain numeric dates.

Re: Date comparison in LOAD not working

Thu, 31 Jan 2013 15:36:37 GMT

Gysbert,

Thanks for your quick response. You were right on here. Seems I had to convert the DISC_DATE to numeric on the LOAD and also convert to variables to numeric in order fo the comparison to work.

Cheers,

Lew

On revisiting this, seems I only had to convert the variable to a numeric...