topic Re: Problem with "Where" clause in QlikView

Problem with "Where" clause

Wed, 11 Jun 2014 12:46:01 GMT

Hello everyone

I have the following code that gives me a problem with the "Where" clause:

SQL SELECT "job_nummer",

jobnr,

"datum_auftrag",

Year ("datum_auftrag") as Jahr,

Month ("datum_auftrag") as Monat

FROM EASY.job where "datum_auftrag" = date;

With the "Where" clause I would like to exclude entries that do not have a date.

Could someone help me with this?

Thank you very much in advance...

Re: Problem with "Where" clause

ashfaq_haseeb — Wed, 11 Jun 2014 12:48:34 GMT

try below

to load null only

SQL SELECT "job_nummer",

jobnr,

"datum_auftrag",

Year ("datum_auftrag") as Jahr,

Month ("datum_auftrag") as Monat

FROM EASY.job where "datum_auftrag" is null;

Update

to exclude null

SQL SELECT "job_nummer",

jobnr,

"datum_auftrag",

Year ("datum_auftrag") as Jahr,

Month ("datum_auftrag") as Monat

FROM EASY.job where "datum_auftrag" <> null;

Regards

ASHFAQ

Re: Problem with "Where" clause

Wed, 11 Jun 2014 12:49:35 GMT

Maybe

From EASY.job where "datum_auftrag" = date and "datum_auftrag" <> NULL;

Re: Problem with "Where" clause

Wed, 11 Jun 2014 12:56:30 GMT

Hm, does not work. Does it matter that the field type is DATE? I seem to have a problem with this particular field. the same expression for jobnr works...

Re: Problem with "Where" clause

Wed, 11 Jun 2014 12:59:27 GMT

How is Datum_auftrag formatted? How is Date formatted? Are they the exact same?

Re: Problem with "Where" clause

eduardo_sommer — Wed, 11 Jun 2014 12:59:32 GMT

Or,

Where "datum_auftrag" is not null

Eduardo

Re: Problem with "Where" clause

rbecher — Wed, 11 Jun 2014 13:00:07 GMT

Hi Jan,

try this:

FROM EASY.job where "datum_auftrag" IS NOT NULL;

Re: Problem with "Where" clause

Anonymous — Wed, 11 Jun 2014 13:01:16 GMT

where "datum_auftrag" is not null

Re: Problem with "Where" clause

Wed, 11 Jun 2014 13:04:09 GMT

seems to be a tricky one. the expression works but does not filter out entries that do not have a date...

Re: Problem with "Where" clause

Wed, 11 Jun 2014 13:05:05 GMT

dd.mm.yyyy

yes, all are the same

Re: Problem with "Where" clause

Wed, 11 Jun 2014 13:07:24 GMT

From EASY.job where "datum_auftrag" IS NOT NULL AND "datum_auftrag" = Date;

Re: Problem with "Where" clause

eduardo_sommer — Wed, 11 Jun 2014 13:12:17 GMT

What brand of SQL are you using. If it is Microsoft SQL server. it's Transact-SQL has a function called ISDATE, which you can use to check if your field contains a valid date.

Eduardo

Re: Problem with "Where" clause

Anonymous — Wed, 11 Jun 2014 13:13:31 GMT

It cannot be tricky... Maybe there are NULLs and BLANKs there (?). Try this

where ltrim(rtrim("datum_auftrag")) <> '' and "datum_auftrag" is not null

Re: Problem with "Where" clause

Wed, 11 Jun 2014 13:20:22 GMT

Hi Eduardo?

How does the ISDATE funktion work?

Best

Jan

Re: Problem with "Where" clause

eduardo_sommer — Wed, 11 Jun 2014 13:24:05 GMT

Where ISDATE("datum_auftrag") = 1

If ISDATE returns 1, the date is valid.

Eduardo

Re: Problem with "Where" clause

Wed, 11 Jun 2014 13:48:32 GMT

It is an odbc database is guess it does not have this funktion.

Re: Problem with "Where" clause

eduardo_sommer — Wed, 11 Jun 2014 13:51:42 GMT

Hi Jan,

ODBC is just the link between your application and the database. It is not "the database". So, depending on the underlying database, you will have different command sets.

Eduardo

Re: Problem with "Where" clause

Wed, 11 Jun 2014 14:01:00 GMT

ok, my apologies. it is a pervasive database. plaese excuse my mistake. this is quite new to me...

Re: Problem with "Where" clause

Wed, 11 Jun 2014 14:02:38 GMT

FROM EASY.job Where "datum_auftrag" is null;

works. it gives me the empy fields.

FROM EASY.job Where "datum_auftrag" is not null;

works too, but it does not filter out the empty fiels, they are still there...

Re: Problem with "Where" clause

eduardo_sommer — Wed, 11 Jun 2014 14:09:13 GMT

I made a quick research on the IsDate function for Pervasive. It works a little different from T-SQL. Instead of returning 1 for true and 0 for false it just returns true or false.

So, please try using

Where ISDATE(datum_auftrag)

without the '= 1' I suggested before.

Eduardo