Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

philmywallet
New Contributor III

Where statement

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

Tags (1)
7 Replies
ahaahaaha
Honored Contributor

Re: Where statement

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

Highlighted
MVP
MVP

Re: Where statement

What you mentioned isn't working for you?

juan_patrick
Contributor

Re: Where statement

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.

philmywallet
New Contributor III

Re: Where statement

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?

philmywallet
New Contributor III

Re: Where statement

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

MVP
MVP

Re: Where statement

May be this

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

juan_patrick
Contributor

Re: Where statement

Load *

from table

where

company = 'ABCD' and

(

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

  (createdate > 365)

);