topic Re: Where Clause using OR and AND not working in QlikView

Where Clause using OR and AND not working

crystles — Mon, 17 Oct 2016 15:33:20 GMT

I have a where clause that I need to filter the data set for specific users AND also only show two possible statuses for those specific users.

Here is my Where clause with just the users filtered for.

Where User = 'John'

or User = 'Sally'

or User = 'Beth'

But what I need is to add a filter for the Status field but also keep the specified users... Here is what I have been trying but it won't actually filter the Status Field, it keeps showing all the statuses for the users specified.

Test 1

Where User = 'John'

or User = 'Sally'

or User = 'Beth'

and Match(Status,'Past Due', 'In Process')

Test 2

Where User = 'John'

or User = 'Sally'

or User = 'Beth'

and Status='Past Due' or Status= 'In Process'

Test 3

Where Status='Past Due' or Status= 'In Process'

and User = 'John'

or User = 'Sally'

or User = 'Beth'

NONE of these work. I am sure something is wrong with my syntax. Any help or advice would be greatly appreciated.

Thanks!

Re: Where Clause using OR and AND not working

Peter_Cammaert — Mon, 17 Oct 2016 15:36:06 GMT

Use parentheses to group the OR clauses, or use two Match() calls instead, like

Where ((User = 'John') or

(User = 'Sally') or

(User = 'Beth')) and

Match(Status, 'Past Due', 'In Process')

Where Match(User, 'John', 'Sally', 'Beth') and

Match(Status, 'Past Due', 'In Process')

Re: Where Clause using OR and AND not working

sunny_talwar — Mon, 17 Oct 2016 15:37:40 GMT

Are you doing this in SQL or QlikView's Load script?

For QlikView, try this:

Where Match(User, 'John', 'Sally', 'Beth') and Match(Status,'Past Due', 'In Process');

For SQL, may be this:

Where User in ('John', 'Sally', 'Beth') and Status in ('Past Due', 'In Process');

Re: Where Clause using OR and AND not working

crystles — Mon, 17 Oct 2016 15:47:38 GMT

Both of you were correct, but I have to mark the person who commented first as correct just to be fair.

I used the two match statements and that worked perfectly.(see below) Thank you to you both!

Where Match(User, 'John','Sally','Beth')

and

Match(Status,'Past Due','In Progress')

Re: Where Clause using OR and AND not working

FunmiOdemuyiwa — Thu, 08 Sep 2022 19:36:10 GMT

This is very useful. I have found QlikSense to be super cubersome. I previously used PowerBI, Tableau, Pandas but QlikSense and QlikSense resources are frustrating.