topic Re: Multiple WHEREs, ORs, ANDs in QlikView

Multiple WHEREs, ORs, ANDs

blpetosa — Thu, 10 Aug 2017 18:14:22 GMT

Hello Everyone,

At the end of my load script, I have the following resident load with multiple criteria at the end. The AND statement at the end makes sure that the same email isn't sent out 2 weeks in a row ("isnull" is in case they never went out). All the statements above the AND seem to work fine, but the AND statement won't work unless I comment the rest of them out. I am hoping it is just a simple syntax error, and appreciate any help that comes my way.

Resident:

Load

segment_id,

event,

funded,

is_published,

Goal,

Published_Date,

Expiration_Date,

Title,

"Contracted Email Features",

"Current Email Features",

launch_date,

is_feature

RESIDENT QVDData

WHERE (Published_Date > TODAY()-365)

OR (Published_Date >= Today() and Published_Date <= $(thisweek))

OR (Expiration_Date >= Today() and Expiration_Date < weekstart(TODAY(), 2, 0))

OR ("Contracted Email Features" - "Current Email Features" > 0)

OR (event/Goal <.3 and Published_Date<TODAY()-90)

AND (launch_date < TODAY() - 7 or isnull(launch_date))

;

DROP TABLE QVDData

;

Re: Multiple WHEREs, ORs, ANDs

sunny_talwar — Thu, 10 Aug 2017 18:21:53 GMT

May be try this

WHERE ((Published_Date > TODAY()-365)

OR (Published_Date >= Today() and Published_Date <= $(thisweek))

OR (Expiration_Date >= Today() and Expiration_Date < weekstart(TODAY(), 2, 0))

OR ("Contracted Email Features" - "Current Email Features" > 0)

OR (event/Goal <.3 and Published_Date<TODAY()-90))

AND (launch_date < TODAY() - 7 or isnull(launch_date))

Re: Multiple WHEREs, ORs, ANDs

Oleg_Troyansky — Thu, 10 Aug 2017 18:25:16 GMT

I think it has to do with the order of operations. Think of the OR operator as a "+" and the AND operator as a "*". So, in the following expression:

A + B + C + D*E ,

the multiplication of E only applies to D, but not to A, B, and C. If you want your last AND condition to be applied on top of all the OR conditions, then you need to enclose all the or conditions in a set of parentheses:

(A + B + C + D)*E

Translating that to your syntax:

WHERE

(

(Published_Date > TODAY()-365)

OR (Published_Date >= Today() and Published_Date <= $(thisweek))

OR (Expiration_Date >= Today() and Expiration_Date < weekstart(TODAY(), 2, 0))

OR ("Contracted Email Features" - "Current Email Features" > 0)

OR (event/Goal <.3 and Published_Date<TODAY()-90)

)

AND (launch_date < TODAY() - 7 or isnull(launch_date))

This should work as you expected.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!

Re: Multiple WHEREs, ORs, ANDs

Oleg_Troyansky — Thu, 10 Aug 2017 18:27:44 GMT

Sunny, I think I'll just take it easy today, you've got it all covered 🙂

Re: Multiple WHEREs, ORs, ANDs

effinty2112 — Thu, 10 Aug 2017 18:29:55 GMT

Hi Ben,

I think you need another pair of brackets, if I understand your logic that is.

Try:

WHERE (

(Published_Date > TODAY()-365)

OR (Published_Date >= Today() and Published_Date <= $(thisweek))

OR (Expiration_Date >= Today() and Expiration_Date < weekstart(TODAY(), 2, 0))

OR ("Contracted Email Features" - "Current Email Features" > 0)

OR (event/Goal <.3 and Published_Date<TODAY()-90)

)

AND (launch_date < TODAY() - 7 or isnull(launch_date))

Good luck

Andrew

Re: Multiple WHEREs, ORs, ANDs

blpetosa — Thu, 10 Aug 2017 18:38:10 GMT

Worked like a charm! Good to know going forward. Thanks for the swift reply!

Re: Multiple WHEREs, ORs, ANDs

blpetosa — Thu, 10 Aug 2017 18:39:25 GMT

Thanks Oleg, it worked perfectly. I took developer training with you about 6 months ago. Thanks for the lesson.....again.

Re: Multiple WHEREs, ORs, ANDs

blpetosa — Thu, 10 Aug 2017 18:39:52 GMT

Thanks to you as well, it worked great.

Re: Multiple WHEREs, ORs, ANDs

sunny_talwar — Thu, 10 Aug 2017 18:41:25 GMT

I am glad we were all able to help

Re: Multiple WHEREs, ORs, ANDs

effinty2112 — Thu, 10 Aug 2017 19:01:23 GMT

Hi Ben,

Glad that's sorted for you. Yes Sunny is quick isn't he? Not only is he the Mozart of QlikView scripting he's the Usain Bolt of posting!