topic Re: Where and and statements not working as expected in Data Movement & Streaming

Where and and statements not working as expected

bruce_sorge — Wed, 19 Mar 2025 21:46:28 GMT

I have a fairly simple load script, but I am having a weird issue. I am using a WHERE statement and an AND statement like below:

WHERE Match([Baseline Status],'Timely') AND Match([Project Officer],'Persons Name') OR Match([Project Officer],'Persons Name')

If I only use the first project officer (PO), I get only records that match Timely If I use my OR statement then I get all records (Timely, Past End Date, etc.). I searched the interwebs and can't figure out why this is happening. Hopefully this is just some silly thing I overlooked.

Thanks

Re: Where and and statements not working as expected

diegozecchini — Fri, 22 Nov 2024 14:46:49 GMT

Hi!
It seems the issue you’re encountering likely stems from how AND and OR are being interpreted in the WHERE clause in Qlik's load script. In Qlik, AND and OR have specific precedence rules that might not align with what you expect.

Your current script:
WHERE Match([Baseline Status], 'Timely')
AND Match([Project Officer], 'Persons Name')
OR Match([Project Officer], 'Persons Name')

is interpreted like this due to operator precedence:

WHERE (Match([Baseline Status], 'Timely') AND Match([Project Officer], 'Persons Name'))
OR Match([Project Officer], 'Persons Name')

This means that any record matching the Project Officer condition (regardless of Baseline Status) will be included.

To solve you need to explicitly define the precedence using parentheses. For example, if you intend to apply the Timely condition to both Project Officer matches, structure it like this:

WHERE Match([Baseline Status], 'Timely')
AND (Match([Project Officer], 'Persons Name') OR Match([Project Officer], 'Another Persons Name'

Re: Where and and statements not working as expected

bruce_sorge — Fri, 22 Nov 2024 14:54:38 GMT

Thank you @diegozecchini, this was the issue. As I suspected, something simple.