Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

blpetosa
New Contributor III

Multiple WHEREs, ORs, ANDs

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

;

1 Solution

Accepted Solutions

Re: Multiple WHEREs, ORs, ANDs

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

9 Replies

Re: Multiple WHEREs, ORs, ANDs

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

MVP
MVP

Re: Multiple WHEREs, ORs, ANDs

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!


MVP
MVP

Re: Multiple WHEREs, ORs, ANDs

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

effinty2112
Honored Contributor

Re: Multiple WHEREs, ORs, ANDs

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
New Contributor III

Re: Multiple WHEREs, ORs, ANDs

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

blpetosa
New Contributor III

Re: Multiple WHEREs, ORs, ANDs

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

blpetosa
New Contributor III

Re: Multiple WHEREs, ORs, ANDs

Thanks to you as well, it worked great.

Re: Multiple WHEREs, ORs, ANDs

I am glad we were all able to help

effinty2112
Honored Contributor

Re: Multiple WHEREs, ORs, ANDs

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!

Community Browser