topic Re: Complex Where Clause in QlikView

Complex Where Clause

Thu, 06 Sep 2012 10:16:54 GMT

Need some help with a WHERE clause - Cant seem to get it right.

I have the follows LOAD script that returns the attached screen shot.

LOAD COUNTRY, YEAR, DATA_SOURCE, AMOUNT

FROM

FILE.qvd

(qvd)

where trim(VIEWID) = 'ABC'

and (ACCOUNT = '100' or ACCOUNT = '101' or ACCOUNT = '102' or ACCOUNT = '103')

and Exists([COUNTRY], ApplyMap('BU_TBL_MAP', COUNTRY, COUNTRY));

Im trying to exclude 2010/2011 data for Ireland since I dont want it coming through into the app. What will the new WHERE clause be?

Thanks, Jay

Re: Complex Where Clause

Thu, 06 Sep 2012 10:37:42 GMT

Hi, one way would be:

where (COUNTRY<>'Ireland' or (YEAR <>2010 and YEAR<>2011));

Re: Complex Where Clause

Thu, 06 Sep 2012 10:42:40 GMT

This perhaps?

In the statement below, I marked in the comment where I added things. It does this:

It excludes Ireland when the year is 2010 or 2011. So Data for Ireland in 2012 will still be visible

LOAD
COUNTRY,
YEAR,
DATA_SOURCE,
AMOUNT
FROM
FILE.qvd
(qvd)
where
trim(VIEWID) = 'ABC'

and (ACCOUNT = '100' or ACCOUNT = '101' or ACCOUNT = '102' or ACCOUNT = '103')
and Exists([COUNTRY],ApplyMap('BU_TBL_MAP',COUNTRY, COUNTRY))
//statement below is added
and not(COUNTRY= 'Ireland' and ( YEAR=2010 OR YEAR=2011))
;

Hope it helps.

Re: Complex Where Clause

Thu, 06 Sep 2012 10:58:54 GMT

Very good...didnt know about the NOT statement.

Jay