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

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?

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