topic Compare dates in Load statement in QlikView

Compare dates in Load statement

Wed, 04 Apr 2012 09:13:48 GMT

I'm trying to add a flag field in a Load statement by comparing a date field in the database to a date in a variable. The variable is set like this:

LET vCompareDate = date(AddMonths(Peek('UptimeDate', -1, 'UptimePerDayTemp'), -1), 'DD/MM/YY');

And the Load statement looks like this:

LOAD

UptimeDate,

If (UptimeDate > $(vCompareDate), 1, 0) as LastMonthFlag

Resident UptimePerDayTemp;

But I get only 1, i.e. the If statement always evaluates to True. Why?

Compare dates in Load statement

Anonymous — Wed, 04 Apr 2012 11:59:53 GMT

Most of the time this is a format issue.

What you should do is compair the format of your field: UptimeDate with the format of your variable $(vCompareDate)

In other words does the data in the field "UptimeDate" the same as when you show your variable $(vCompareDate) (fe) in a inputbox.

Compare dates in Load statement

Wed, 04 Apr 2012 12:32:17 GMT

I added an input box to show the value of the vCompareDate variable and it turned out that the formats differed, so I changed the format of the variable like this:

LET vCompareDate = date(AddMonths(Peek('UptimeDate', -1, 'UptimePerDayTemp3'), -1), 'M/D/YYYY');

But the If statement still only evaluates to True.

The UptimeDate field in the table contains every date from 1/4/2010 to 4/1/2012.

The vCompareDate variable contains "3/1/2012" (1st of March).

Compare dates in Load statement

Anonymous — Wed, 04 Apr 2012 12:40:46 GMT

Does your UptimeDate field als has the format 'M/D/YYYY' ?

(just making sure it is not 'D/M/YYYY' or maybe 'MM/DD/YYYY')

If so try to Floor your date :

Date(FLOOR(UptimeDate) , 'M/D/YYYY')

I am pretty sure it's a format thing...

Compare dates in Load statement

Wed, 04 Apr 2012 13:18:18 GMT

Yes, they both have the format 'M/D/YYYY'. I tried your suggestion without any luck, but then I tried using Num instead (since you thought it was a format thing), and now it works.

The new variable assignment:

LET vCompareDate = Num(AddMonths(Peek('UptimeDate', -1, 'UptimePerDayTemp'), -1));

The new Load statement:

LOAD

UptimeDate,

If (Num(UptimeDate) > $(vCompareDate), 1, 0) as LastMonthFlag

Resident UptimePerDayTemp;

Thanks for your help!