Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
crystles
Partner - Creator III
Partner - Creator III

Where Clause using OR and AND not working

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!

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

or

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

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

View solution in original post

4 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

or

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

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

sunny_talwar

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

crystles
Partner - Creator III
Partner - Creator III
Author

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

FunmiOdemuyiwa
Contributor
Contributor

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.