topic Re: Where Conditions in QlikView

Where Conditions

Mon, 24 Aug 2015 09:05:50 GMT

Hiya,

Can anyone tell me where I'm going wrong at the end of my query I am using the following where clause:

Where usage_date > (date(today()-31,'DD/MM/YYYY'))

and usage_date < date(today(),'DD/MM/YYYY')

and Not isnull(usage_date)

It is failing on the second line, the error it is giving me is field "and" not recognised :S ???!!!

Re: Where Conditions

puttemans — Mon, 24 Aug 2015 09:08:27 GMT

I'd play around with the brackets

Where (usage_date > (date(today()-31,'DD/MM/YYYY'))

and usage_date < date(today(),'DD/MM/YYYY')

and Not isnull(usage_date))

Re: Where Conditions

Anonymous — Mon, 24 Aug 2015 09:08:47 GMT

Post your full query .

Re: Where Conditions

Digvijay_Singh — Mon, 24 Aug 2015 09:11:53 GMT

Looks like you got extra bracket after first condition..

Re: Where Conditions

Digvijay_Singh — Mon, 24 Aug 2015 09:14:38 GMT

Oh, missed that, there is one before date as well.

Re: Where Conditions

Mon, 24 Aug 2015 09:24:41 GMT

thanks I hadn't spotted that

I tried again with this script, still same error 😕

QualifY *;
Unqualify clarapw_id;
CARE:
Load
DateTime,
clarapw_id,
usage_date,
bytes_downloaded,
bytes_uploaded
Resident clarapw_usage

Where usage_date > date(today()-31,'DD/MM/YYYY')

and usage_date < date(today(),'DD/MM/YYYY')

and Not isnull(usage_date)

Order by usage_date asc;

Re: Where Conditions

Anonymous — Mon, 24 Aug 2015 09:26:10 GMT

Hi,

Try:

Where usage_date > date(today()-31,'DD/MM/YYYY')

and usage_date < date(today(),'DD/MM/YYYY')

and Not isnull(usage_date)

Re: Where Conditions

Anonymous — Mon, 24 Aug 2015 09:30:07 GMT

Hi Sarah,

What is usage_date format?

Regards

Neetha

Re: Where Conditions

Peter_Cammaert — Mon, 24 Aug 2015 09:31:45 GMT

Still works ok in any other document.

Clear the LOAD statement and write it again. Maybe some invisible text character is having fun with you.

Peter

Re: Where Conditions

sasiparupudi1 — Mon, 24 Aug 2015 10:14:14 GMT

please attach your application

Re: Where Conditions

Mon, 24 Aug 2015 10:51:30 GMT

Grr ! lol just retyped the whole thing and is failing at that point. Makes no sense !

Re: Where Conditions

sasiparupudi1 — Mon, 24 Aug 2015 10:55:07 GMT

May be try

QualifY *;

Unqualify clarapw_id;

CARE:

Load

DateTime,

clarapw_id,

usage_date,

bytes_downloaded,

bytes_uploaded

Resident clarapw_usage

Where clarapw_usage.usage_date > date(today()-31,'DD/MM/YYYY')

and clarapw_usage.usage_date < date(today(),'DD/MM/YYYY')

and Not isnull(clarapw_usage.usage_date)

Order by clarapw_usage.usage_date asc;

Re: Where Conditions

Kushal_Chawda — Mon, 24 Aug 2015 10:55:35 GMT

try

Where (date(date#(usage_date,'DD/MM/YYYY'),'DD/MM/YYYY') > date(today()-31,'DD/MM/YYYY')

and date(date#(usage_date,'DD/MM/YYYY'),'DD/MM/YYYY') < date(today(),'DD/MM/YYYY'))

and Not isnull(usage_date)

Re: Where Conditions

marcus_malinow — Mon, 24 Aug 2015 11:24:32 GMT

Sarah,

a couple of things I'd try:

first off I'd change the calculation of the dates so that they are enclosed in a '$(= )', so that they are evaluated rather than passed through to your database. Also note that I've enclosed the entire string in quotes.

Secondly I'd change the format of the date to YYYY-MM-DD as this can not be misinterpreted based on your regional settings.

So this is the where clause:

Where usage_date > '($=date(today()-31,'YYYY-MM-DD'))'

and usage_date < '($=date(today(),'YYYY-MM-DD'))'

and Not isnull(usage_date)

Re: Where Conditions

jonathandienst — Mon, 24 Aug 2015 11:42:09 GMT

As long as the field usage_date is coming in as a proper QV date, using the Date() function is entirely unnecessary. The Not Isnull() will be done implicitly by the other comparisons, so it is also unnecessary. So the condition becomes:

LOAD ...

FROM ...

Where usage_date > today()-31 and usage_date < today();

If usage_date is a string, you need the date conversion function Date#() (NOT Date()) to convert to a date from the comparison:

Where Date#(usage_date, '<format>') > today()-31 and Date#(usage_date, '<format>') < today();

(<format> is the date format of the string usage_date).

These comparisons are numeric, so the regional date format is no longer an issue.

Re: Where Conditions

marcus_malinow — Mon, 24 Aug 2015 11:45:20 GMT

my bad, I thought this was a database query rather than a resident load.

Re: Where Conditions

Tue, 25 Aug 2015 08:40:03 GMT

Thanks guys for your help it turned out to be a variable setting it was referencing further up that was causing the script to fail even though it was giving me the error here. So annoying ! thanks sorry for wasting your time