Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
;
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))
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))
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!
Sunny, I think I'll just take it easy today, you've got it all covered 🙂
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
Worked like a charm! Good to know going forward. Thanks for the swift reply!
Thanks Oleg, it worked perfectly. I took developer training with you about 6 months ago. Thanks for the lesson.....again.
Thanks to you as well, it worked great.
I am glad we were all able to help
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!