topic how to compare two date values stored in variables ? in QlikView

how to compare two date values stored in variables ?

Fri, 15 Jul 2011 08:31:09 GMT

Hello Everybody,

I am trying to compare two date value stored in two variables.

This is what I am trying :

let curr_date= date(today(),'MM/DD/YYYY');

LET PREV_DATE= DATE ('06/07/2011','MM/DD/YYYY');

if date($(PREV_DATE)) < date($(curr_date))

let hilo= 1;

else

LET hilo=0;

end if

I have to assign the value of variable hilo based up on this condition.

But I am getting error

Script line error:

if date(06/07/2011) < date(07/15/2011)

Can anybody help me out and tell me how to do the comparison ?

how to compare two date values stored in variables ?

kaushiknsolanki — Fri, 15 Jul 2011 08:34:25 GMT

HI,

Try without date function. like this.

let curr_date= date(today(),'MM/DD/YYYY');

LET PREV_DATE= DATE ('06/07/2011','MM/DD/YYYY');

if $(PREV_DATE) < $(curr_date)

let hilo= 1;

else

LET hilo=0;

end if

Regards,

Kaushik Solanki

Re: how to compare two date values stored in variables ?

Miguel_Angel_Baeyens — Fri, 15 Jul 2011 08:35:45 GMT

Hello,

If you are doing that in the script, the syntax needs the THEN keyword

IF Date($(PREV_DATE)) < Date($(curr_date)) THEN

That should work

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

how to compare two date values stored in variables ?

Fri, 15 Jul 2011 08:50:24 GMT

Use then after If expression

i.e If condition then

expression

else

end if

how to compare two date values stored in variables ?

Fri, 15 Jul 2011 09:04:01 GMT

Thank you all for you reply.

Sorry I forget to put then .

Now I have tried :

let curr_date= date(today(),'MM/DD/YYYY');

LET PREV_DATE= DATE ('06/07/2011','MM/DD/YYYY');

if date($(PREV_DATE),'MM/DD/YYYY') < date($(curr_date),'MM/DD/YYYY') then

let hilo= 1;

else

LET hilo=0;

end if

But according to the logic the value of hilo must be 1.

But here I am getting value of hilo as 0.

can you explain what is the problem , or where am I going wrong?

Thanks in advance !!!!

Re: how to compare two date values stored in variables ?

Miguel_Angel_Baeyens — Fri, 15 Jul 2011 09:25:48 GMT

Hi,

You are comparing dates, and dates as returned by the Date() function are literals, so you need to single quote them

IF '$(PREV_DATE)' < '$(curr_date)' THEN

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

how to compare two date values stored in variables ?

Fri, 15 Jul 2011 09:32:10 GMT

Thanks for the help.

Can you through some more light on the Date() function, so that I would not do the same mistake again.

I didn't got your point.

Thanks in advance !!!!!

how to compare two date values stored in variables ?

Fri, 15 Jul 2011 09:38:34 GMT

Hi Dear,

Use Correect syntax of if-else condition

Syntax is:- IF(<CONDITION>,<THEN EXPRESSION>,<ELSE EXPRESSION>)

example:- IF(DATE($(PREV_DATE)) < DATE($(CURR_DATE)) , LET hillo=1 , LET hillo=0)

Re: how to compare two date values stored in variables ?

Miguel_Angel_Baeyens — Fri, 15 Jul 2011 09:53:36 GMT

Hi,

It all has to do with the way QlikView uses variables and the difference bewteen LET and SET, and incidentally, dollar expansion. I posted about this some months ago, in this and this post (they came from the old Community, so I just have edited them to keep a readable format).

Besides, a Date() is a literal value, so when you refer to it in a conditional, you will not need to use quotes when it's not expanded, as in

IF Date(PREV_DATE) < Date(curr_date) THEN

Because you are comparing equal formats (numeric formats, a date is evaluable as a numeric value in QlikView). When you expand (you use the $() so the values within are evaluated) and the result is a literal for example "15/07/2011", you do need to quote them

IF '$(PREV_DATE)' < '$(curr_date)' THEN

Check both for further information, and check the Reference Manual in this regard.

Hope that makes sense and helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica