topic Re: Load Where Statement with Multiple Criteria, AND not working in QlikView

Load Where Statement with Multiple Criteria, AND not working

tkw8000 — Thu, 05 Sep 2019 21:30:11 GMT

Trying to load records that have fieldname "CHNG_ORD_SEQ"=1 and fieldname "CHNG_ORD_BATCH"=0, and CHNG_ORD_SEQ=0 and CHNG_ORD_BATCH=1, and anything with either field >0

Only want to eliminate records with zero in both fields.

I have written the code below, but it eliminates any record with a 0 in either field. Any ideas on why the AND is being ignored?

where (OPRID_MODIFIED_BY <> 'POCHNG')

and ("CHNG_ORD_SEQ"<>'0') and ("CHNG_ORD_BATCH"<>'0');

Thanks,

Re: Load Where Statement with Multiple Criteria, AND not working

Vegar — Thu, 05 Sep 2019 21:52:00 GMT

Try to handle the zero as numeric, no ' ' around the 0.

where (OPRID_MODIFIED_BY <> 'POCHNG') and ("CHNG_ORD_SEQ"<>0) and ("CHNG_ORD_BATCH"<>0);

Re: Load Where Statement with Multiple Criteria, AND not working

rwunderlich — Thu, 05 Sep 2019 22:55:13 GMT

where (OPRID_MODIFIED_BY <> 'POCHNG')

and ( ("CHNG_ORD_SEQ"<>'0') or ("CHNG_ORD_BATCH"<>'0') );

I believe that should be an "or". Also note the extra parens required.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Load Where Statement with Multiple Criteria, AND not working

tkw8000 — Fri, 06 Sep 2019 16:53:02 GMT

Thanks for the reply.

Re: Load Where Statement with Multiple Criteria, AND not working

tkw8000 — Fri, 06 Sep 2019 17:40:20 GMT

Thank you but this did not work.

Re: Load Where Statement with Multiple Criteria, AND not working

tkw8000 — Fri, 06 Sep 2019 17:41:21 GMT

Sorry but this did not work. Do you think I should use an IF statement instead?

Re: Load Where Statement with Multiple Criteria, AND not working

Vegar — Fri, 06 Sep 2019 18:52:42 GMT

It feels like there is something with your scenario that is not taken into consideration. Har to pinpoint without the actual application at hands.

You could give this a try

where

(OPRID_MODIFIED_BY <> 'POCHNG')

and

RangeSum("CHNG_ORD_SEQ", "CHNG_ORD_BATCH")>0;

Re: Load Where Statement with Multiple Criteria, AND not working

rwunderlich — Fri, 06 Sep 2019 20:53:10 GMT

The best way to sort this out to load all the data (or a limited load of some number of rows). Put the Fields as Dimensions in a straight table and the where clause (everything but "where") as expression. See what it returns and play with it until you get -1 for the rows you want and 0 for the rows you don't.

-Rob