topic Re: Where statement in QlikView

Where statement

Anonymous — Wed, 28 Jun 2017 13:27:34 GMT

Can anyone help. I need a where statement in my script which only includes any rows with the following criteria :

All data for company 'ABCD' where the status is NOT in authorised status and the create date > 365 ago

The script below does not work.

Where Company = 'ABCD' and

(Status<>'Authorised' and CreateDate>=(Today()-365))

Thoughts anyone?

Please help

Thanks

Phil

Re: Where statement

ahaahaaha — Wed, 28 Jun 2017 13:35:46 GMT

Hi Phil ,

Try

Where Company = 'ABCD' and Status<>'Authorised' and CreateDate>=Date((Today()-365)) Or

Where Company = 'ABCD' and Status<>'Authorised' and Num(CreateDate)>=(Today()-365)

Andrey

Re: Where statement

sunny_talwar — Wed, 28 Jun 2017 13:38:59 GMT

What you mentioned isn't working for you?

Re: Where statement

juan_patrick — Wed, 28 Jun 2017 13:53:34 GMT

hi,

Is case sensitive,check that or you can do this:

where upper(Status) <>'AUTHORISED'

BETTER:

WHERE

NOT MATCH(upper(Status), 'AUTHORISED')

the other thing is the format of the date field, but i think thats working.

Re: Where statement

Anonymous — Wed, 28 Jun 2017 14:00:08 GMT

The above suggestions dont work.

My actual where statement works, but it excludes all authorised and shows all records which are less than 12 months old.

What I want is for it to show all records with an exception of the records less than 12 months old which still have a status 'authorisation'.

i.e. If the record is less than 12 months old then it still needs to show the authorised fields.

Does that make sense?

Re: Where statement

Anonymous — Wed, 28 Jun 2017 14:00:51 GMT

each statement works on it's own, so it can't be a syntax error

Re: Where statement

sunny_talwar — Wed, 28 Jun 2017 14:05:04 GMT

May be this

Where (Company = 'ABCD' and Status<>'Authorised' and CreateDate >= Today()-365) or (Company = 'ABCD' and Status = 'Authorised' and CreateDate < Today()-365);

Re: Where statement

juan_patrick — Wed, 28 Jun 2017 14:09:01 GMT

Load *

from table

where

company = 'ABCD' and

(

(createdate <= 365 and status <> 'authorised') or

(createdate > 365)

);