Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
heitorcarlosgomes
Contributor II
Contributor II

WHERE NOT with multiple criteria

Hey everyone

I'm trouble with Where Not


WHERE NOT

(

(FABRICANTE = 'FACCHINI' or FABRICANTE = 'RANDON')

or (FAMÍLIA = 'DOLLY' or FAMÍLIA = 'CARGA SECA' or FAMÍLIA = 'CANAVIEIRO')

or ([REBOQUE/SEMIRREBOQUE] = 'REBOQUE')

);

 

 

I don't know why my output contains all values that I don't want

 

How can I fix?

Labels (2)
6 Replies
Vegar
MVP
MVP

what are you trying to do? 

Using WHER NOT in combination with nested OR statements can be troublesome. Remember that  as  long one of your FABRICANTE, FAMILIA or REBOQUE criterias is not true for a transaction the data will be included in the output.

Could it be that you want to do this?

WHERE NOT

(

(FABRICANTE = 'FACCHINI' or FABRICANTE = 'RANDON')

AND (FAMÍLIA = 'DOLLY' or FAMÍLIA = 'CARGA SECA' or FAMÍLIA = 'CANAVIEIRO')

AND ([REBOQUE/SEMIRREBOQUE] = 'REBOQUE')

);

heitorcarlosgomes
Contributor II
Contributor II
Author

Actually, I've written 'and' instead 'or' and I'm still having the same problem.

 

heitorcarlosgomes
Contributor II
Contributor II
Author

Your solution is not working, unfortunately...

 

 

Vegar
MVP
MVP

@heitorcarlosgomes 

Can you try to describe in word how your where statement should filter the data you want?

PrashantSangle

try with not wildmatch()

try below

not wildmatch(FABRICANTE,'FACCHINI','RANDON')

and not wildmatch(FAMÍLIA,'DOLLY','CARGA SECA','CANAVIEIRO')

and not wildmatch([REBOQUE/SEMIRREBOQUE],'REBOQUE')

 

since we are not familiar with your data & output which you are looking for. So, Choose and / or as per your need. also, you can use bracket to club to condition in one group.

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Aasir
Creator III
Creator III

Smaller change done to your query

  • UPPER to make the comparisons case-insensitive.
  • added checks for NULL values in the columns.


SELECT *
FROM YourTable
WHERE NOT (
(UPPER(FABRICANTE) = 'FACCHINI' OR UPPER(FABRICANTE) = 'RANDON')
OR (UPPER(FAMÍLIA) = 'DOLLY' OR UPPER(FAMÍLIA) = 'CARGA SECA' OR UPPER(FAMÍLIA) = 'CANAVIEIRO')
OR (UPPER([REBOQUE/SEMIRREBOQUE]) = 'REBOQUE')
)
AND FABRICANTE IS NOT NULL
AND FAMÍLIA IS NOT NULL
AND [REBOQUE/SEMIRREBOQUE] IS NOT NULL;