topic where field between variable and variable in QlikView

where field between variable and variable

Tue, 07 Jul 2009 15:25:35 GMT

I want to load data between two predefined dates in the script.

I have the following code but something does not work properly in the where clausule:

//************************************************
DATE_MAP:
MAPPING
LOAD
date
date(GoodDate, 'DD-MM-YYYY') as Date
FROM ...........
WHERE GoodDate >= $(STARTDATE) and
GoodDate >= $(ENDDATE);

Any suggestions? thanks!

where field between variable and variable

Tue, 07 Jul 2009 16:36:39 GMT

Should you comparison tests be >= $(STARTDATE) and <= $(ENDDATE) ?

Regards,

Gordon

where field between variable and variable

Tue, 07 Jul 2009 16:54:07 GMT

of course enddate is <= ......little copy paste mistake ;-)) ...

the original looks like that but when I reload zero lines will fetch while I'm sure there are over 3000 records between the start and enddate.

//************************************************
DATE_MAP:
MAPPING
LOAD
date
date(GoodDate, 'DD-MM-YYYY') as Date
FROM ...........
WHERE GoodDate >= $(STARTDATE) and
GoodDate <= $(ENDDATE);

where field between variable and variable

Tue, 07 Jul 2009 17:08:59 GMT

What are the values of the variables STARTDATE and ENDDATE? What is the format of GoodDate in the data? It would seem there is a mismatch.

If STARTDATE and ENDDATE are dates in the format DD-MM-YYYY then you need to format them for the where condition:

WHERE date(GoodDate, 'DD-MM-YYYY) >= $(STARTDATE) and

date(GoodDate, 'DD-MM-YYYY) <= $(ENDDATE)

If this resolves the problem you might also want to consider a 'preceeding load' to keep things tidier.

Regards,

Gordon

where field between variable and variable

stephencredmond — Tue, 07 Jul 2009 17:39:02 GMT

Hi,

Depending on the values that you have, you may simply need some single quotes on the dates:


DATE_MAP:
MAPPING
 LOAD
date
date(GoodDate, 'DD-MM-YYYY') as Date
FROM ...........
WHERE GoodDate >= '$(STARTDATE)' and
 GoodDate <= '$(ENDDATE)';

Regards,

Stephen

where field between variable and variable

Tue, 07 Jul 2009 17:51:41 GMT

Thanks, single quotes around the startdate and enddate did the trick..