Multiple WHEREs, ORs, ANDs

[blpetosa]

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

;

[sunny_talwar · Accepted solution]

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))

[Oleg_Troyansky]

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!

[Oleg_Troyansky]

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

[effinty2112]

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

[blpetosa]

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

[blpetosa]

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

[blpetosa]

Thanks to you as well, it worked great.

[sunny_talwar]

I am glad we were all able to help

[effinty2112]

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!