topic Date Comparison in QlikView

Date Comparison

Fri, 29 Jul 2011 19:38:38 GMT

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.

Re: Date Comparison

nagaiank — Fri, 29 Jul 2011 19:53:22 GMT

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;

Date Comparison

swuehl — Fri, 29 Jul 2011 20:33:55 GMT

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

Re: Date Comparison

Fri, 29 Jul 2011 21:17:58 GMT

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;

Re: Date Comparison

swuehl — Fri, 29 Jul 2011 21:39:40 GMT

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

Re: Date Comparison

swuehl — Fri, 29 Jul 2011 21:43:59 GMT

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;

Date Comparison

swuehl — Fri, 29 Jul 2011 21:55:17 GMT

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

but you should access the value using

fieldvalue( 'CheckDt', 1 )

Regards,

Stefan

Date Comparison

Mon, 01 Aug 2011 20:34:47 GMT

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