Date Comparison

[#1]

I'm trying to compare a couple of dates.

CheckDt was created with the following script:  date(Today(),'MM/DD/YYYY') as CheckDt

I'm trying to do a comparisson to identify if the QVD had been updated Today with the following in the script:

if num(CheckDt) = num(Today()), THEN

     Set vCHECK = 1;

Else

     Set vCHECK = 0;

End if;

It keeps coming up with 0.  I've changed the format of the fields about a 1/2 dozen ways but can't get it set the vCHECK = 1.  When doing the comparison via sheet object, the comparison is true. I've done date#, date, num, num#....I'm baffled. Any help is appreciated.

PS, we just upgraded to QV10...if that matters.

[nagaiank]

The following script returns 1 for the variable vCHECK for me in QlikView Version 10 for me.

LET vCHECK = '';

LET CheckDt = Date(Today(),'MM/DD/YYYY');

if num(CheckDt) = num(Date(Today())) THEN

     Set vCHECK = 1;

Else

     Set vCHECK = 0;

End if;

[swuehl]

hi Rob,

you don't have a comma before THEN in your real script, have you?

Should give you a syntax error and discards the execution of the follwing statements.

Stefan

[#4]

No it's not in the script...just a typo here...but good catch.

krishnamoorthy, your logic works...but only in a new document.  With my existing document it doesn't.  I'm my real script I'm doing a max(LOAD_DATE) as CheckDT before the comparison logic.  I can't see why that would be a problem, but maybe it is? 

Here's the actual script:

Check:
load (LOAD_DATE) from $(QVDFILE) (qvd);

Comp:
Load max(LOAD_DATE) as CheckDt Resident Check;

Drop Table Check;

if num(CheckDt) = num(date(TODAY(),'MM/DD/YYYY')) THEN

     SET CHECK = 1;
else
     set CHECK = 0;

End if;

exit script;

[swuehl]

Hi Rob,

don't you need a group by clause if you want to use max in the script?

I think you can alternatively use order by and then peek to get the max date.

Stefan

[swuehl]

Like:

Check:

load (LOAD_DATE) from $(QVDFILE) (qvd);

Comp:

Load * as CheckDt Resident Check order by LOAD_DATE;

Drop Table Check;

if num(peek('CheckDt',-1,'Comp') = num(date(TODAY(),'MM/DD/YYYY')) THEN

     SET CHECK = 1;

else

     set CHECK = 0;

[swuehl · Accepted solution]

Well, ok, I think you can use max() in the script,

but you should access the value using

fieldvalue( 'CheckDt', 1 )

Regards,

Stefan

[#8]

Thanks.  I didn't think about the multiple rows and they were the thing that was getting me.  Appreciated.