Join us in Las Vegas on June 13-16 for the Snowflake Summit

Learn More
Get Started Transforming Your Data in Snowflake - feature img

Qlik Set Analysis – Using “OR” operator and “Does not equal”

  • How-Tos FAQs
  • November 24, 2020

We often encounter the requirement to include values or exclude based on certain conditions where records meet X or Y criteria. Or where records do NOT meet specific criteria. 

Qlik Set Analysis – Using “OR” operator and “Does not equal”

Remember that Qlik Set Analysis is effectively an automatic method of making User selections. How can we ensure we follow through with the requirement to make the correct selections in Set Analysis?

In the LOAD editor, we have the option to use the NOT MATCH() function. 

Specifically, when joining an additional table to a resident table, the NOT MATCH function is helpful to specify which values should be read into the join. As one would do in a SQL query using NOT IN ().

But how can we enforce that same method in Set Analysis?

We use the exclusion operator to OMIT specific values: 

FIELD -= {VALUES}

Status -= {“Closed”, “Reassigned”, “Stalled”}>}

Well, that’s a great way to display items that are not equal or are precisely equal to particular values. But how do we return values that meet one condition OR another?

Transform Data in Snowflake

The only multi persona SaaS solution for data transformation in Snowflake

Learn more

The simple and direct method is to add each of the conditions within the Expression each contained within their opening and closing tags:

Count({<Year={2021}>+<Status-={“Closed”, “Reassigned”, “Stalled”}> TicketID)

It creates a UNION of the two sets and returns all conditions that meet either condition—the equivalent of an OR statement. 

Reverting again to Possible and Excluded values, P() and E() functions for Set Analysis, is a helpful method of enforcing OR operators. See more detail on these functions here: What is P() & E() used in Qlik?

Using multiple Excluded or Possible values within Set Analysis is achieved similarly to a union of numerous conditions. In the Expression, ADD the possible or excluded values 

Count(<${Year = {'>2021'}+ E({$<Status = {“Closed”, “Reassigned”, “Stalled”}>}+ E({$<Department = {“Systems}>})>} TicketID)  

So to effectively manage an OR situation, we effectively ADD each condition in the Expression to produce the desired results. We list items that do not equal, either with the excluded values function E() or the exclusion operator -=.


Up Next: Learn Qlikview document synching with URL parameters

More Resources We Think You Might Like

Qlik

Advanced Qlik Set Analysis

In our previous article, Understanding Qlik Set Analysis, we covered the basics required to under...

  • How-Tos FAQs
  • November 9, 2020
Qlik

Current Selections in a Qlik Chart Title

Current Selections in a Qlik Chart Title You have created a chart, and you want Current Selection...

  • How-Tos FAQs
  • November 24, 2020
Qlik

GeoAnalytics and Google Maps Extensions for Qlik

Through the years… we’ve experienced many different options for Google Maps Extensions and ...

  • How-Tos FAQs
  • November 15, 2020