Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Date comparison in LOAD not working

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

1 Solution

Accepted Solutions

Re: Date comparison in LOAD not working

$(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.


talk is cheap, supply exceeds demand
2 Replies

Re: Date comparison in LOAD not working

$(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.


talk is cheap, supply exceeds demand
Not applicable

Re: Date comparison in LOAD not working

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... 

Community Browser