How to Filter One or Multiple Values in Alteryx

Alteryx gives users a number of different ways to filter data. Basic filtering can be done with a Filter tool, but the tool can further be customized with various operators. When working with complex scenarios you can utilize advanced techniques that combine tools that are typically reserved for other purposes.

Filtering multiple values in Alteryx can be performed with several different tools and formulas with the filter tool being the primary one

Filtering is an essential skill and while there are many different ways to filter data, it’s important to keep in mind maintainability of a workflow. While it’s technically possible to write a formula that includes 20+ filtering criteria, it may not be manageable in the future when criteria changes. We’ll explain the methods and some tips to keep in mind.

Let’s roll!

Applying a Filter in Alteryx

To apply a filter to data in Alteryx, connect a Filter Tool from the Preparation section of the Alteryx toolbar to your workflow. Configure a filter using a Basic Filter and the dropdown menus, or switch to a Custom Filter for advanced multi-condition filtering using formulas with AND or OR operators.

The Filter tool is available in the Preparation section of the Alteryx Toolbar.

The Filter Tool in the Preparation section of the Alteryx toolbar

There are two outputs of a Filter tool. True (T) and False (F). The filter checks for a condition or conditions that are defined in the tool’s configuration. Then the data flow is split between records that meet the conditions (True) and ones that do not (False).

When you add the next step to your workflow, you can choose to move forward from the True or False output. It’s common to use both outputs for analysis or to run through different workflows.

A filter tool highlighting two different outputs, true and false

Filters are configured in Basic Filter or Custom Filter mode. The Basic Filter uses dropdown menus to select a field, a condition, and a text box that you can type the condition in for.

Basic Filters are data type aware, meaning that it will give you different options if the incoming data field is numeric, string (text), or a date.

Setting a Basic Filter in Alteryx

Once the filter is applied, run the workflow. Make sure you click on the correct, True or False Output when viewing your results. If you look at the wrong output, it may at first glance look like the filter didn’t work as expected.

Example Output of a Basic Filter configured Filter Tool

To further filter data down, you can chain Filter Tools together.

Chaining Multiple Filter Tools Together

The simplest way to filter on multiple criteria in Alteryx is to connect multiple formula tools together. This method can be beneficial because it’s highly documented as each tool has a notation of what the filter is, and you can see step by step the number of records that are reduced by each layer of filtering.

Example of connecting multiple filter tools together

For straight forward filtering scenarios, we would prefer this, but recognize that it is not always practical when working with large sets of filters or when multiple criteria could be met and the results need to be filtered more dynamically.

Filtering Using AND or OR Operators

Alteryx supports the use of AND OR operators to write more complicated conditions to filter. To use them, add a Filter Tool to your workflow. In the configuration panel, select “Custom Filter”. This provides a formula box where you can write multi-condition formulas to filter by.

In the example below, we combined 3 filtering criteria using an AND operator. Each row is a different filtering condition.

You do have to write the column name surrounded by [Square Brackets] for each condition you are writing.

To make things easier to read, we put each part of the formula on a separate row, but this step is entirely optional you can use as many or as few spaces as you want in the filter formula box.

Using a Custom Filter formula to re-create chained filters in a single tool with multiple criteria

OR Statements work in a similar fashion. They tell Alteryx to look for either condition to be true independent of the other one. We still have to list out the full field name surrounded by square brackets for each criteria in this method.

Writing OR statements for muli-criteria filtering in Alteryx

It’s also possible to combine different operators AND and OR within the same formula though it can take some trial and error to get the logic to exactly match how you want the filter to execute and at times it can be easier to split the logic into multiple steps with multiple tools.

You also lose some of the annotations when placing one large filtering formula into a single tool compared to using multiple tools which can make it more challenging for others to understand your workflow.

Alteryx Null, Does Not Equal, Empty Filtering

Alteryx supports a number of special formulas for filtering. To filter on Null values use the formula: IsNull([Field Name]). Check for blank values with: IsEmpty[Field Name] and test when a value does not equal using !=. The methods can be combined, such as: !IsNull([Field Name] to test Is Not Null.

A shortcut to remember these is to set the basic filter and let Alteryx auto generate the formula for you. Then switch to a Custom Filter to adjust the formula that it made as a starting point.

Filter Multiple Criteria Using IN Statements

To Filter Alteryx based on a list of criteria, use an IN Statement. They are common in SQL and allow you to write a list of criteria where each value is surrounded by quotes and separated by commas. Alteryx returns a True Filter when any of the criteria are present.

In the example below, we filtered the Region field based on 3 different States. This could have also been completed by writing a custom filter formula using the OR operator, but it is much cleaner and easier to maintain when wrapped inside of an IN statement.

Using an IN statement in an Alteryx Filter to filter on multiple values

To filter based on items that are not in a list use a “NOT IN” statement, instead of “IN”

Filtering Many Criteria with a Join Tool

To filter based on long lists of criteria, you can use a Join tool in Alteryx to Join a data set with a list of values to filter based off of. The Filtering list could be prepped in Alteryx or by importing a separate Excel file. The Join tool can be configured to match the values in each column, and matching records will output to the Join (J) Output.

The added benefit of using a Join Tool is that it also shows non-matching records from each list using the Left (L) and Right (R) outputs.

The setup would look like the following, where our Filter list contains 3 Regions we want to filter based off of.

Using a Join tool to filter data based on a list of values

The J Output of the Join Tool shows that we have 31 records from our initial data set that meet the criteria.

Example Join (J) output of Alteryx showing filtered values based on a list from a different data set

In more complex scenarios, you can tell Alteryx to join based on values in multiple columns of a list of filtering values. The Join tool is also recommended when criteria is subject to change over time, it’s typically easier to maintain a single file or Text Input tool than it is to modify filter tools with long embedded formulas.

A similar method can also be used using a combination of a Find and Replace and Filter tool if you want to identify which criteria a specific row matched with.

Conclusion

Filtering in Alteryx can be performed in a number of ways. Basic filters can be setup using dropdown menus and the Filter tool. You can choose a Custom Filter in a Filter Tool to write your own custom formulas, using AND, OR, IN statements and modifiers such as does not equal to !=. It gives you the greatest level of control over filtering.

For filtering large amounts of criteria, you could either use an IN statement, chain multiple Filtering Tools together or utilize a Join tool and a separate list to make it easier to maintain.

Scroll to Top