Filter Tool
Filter Tool
One Tool Example
Filter has a One Tool Example. Visit Sample Workflows to learn how to access this and many other examples directly in Alteryx Designer.
Use Filter to select data using a condition. Rows of data that meet the condition are output to the True anchor. Rows of data that do not meet the condition are output to the False anchor.
The Filter tool can...
Select rows by comparing a column against a static value. See an example.
Select rows with or without missing data. See an example.
Select rows using date-time data. See an example.
Select rows using a multiple-column condition. See an example.
Select rows using a compound condition. See an example.
Tool Components
The Filter tool has 3* anchors
Input anchor: Use the input anchor to select the data you want to filter.
T (True) anchor: The True anchor outputs the rows of data that meet the filter condition.
F (False) anchor: The False anchor outputs the rows of data that do not meet the filter condition.
*Because the Filter tool includes an expression editor, an additional input anchor displays when you use Filter in an app or macro. Use the Interface tools to connect to a Question anchor.
Configure the Tool
Select Basic filter or Custom filter.
Use the Basic filter to build a simple query on a single column of data.
Use the Custom filter to build complex conditions or conditions using more than one column.
Build a Basic Filter
Select the column of data to filter by in the Select column dropdown.
Select the operator in the next dropdown. Available operators depend on the data type of the column you selected to filter by. See Basic Filter Operators below.
Enter a value to complete the condition.
Basic Filter Operators
Available operators depend on the data type of the column you select to filter by. Learn more about Data Types.
Operator | Description | Available Data Types |
|---|---|---|
= | Value matches input. | Numeric, Date, DateTime |
Equals | Value matches input. | String |
!= | Value does not match input. | Numeric, Date, DateTime |
Does not equal | Value does not match input. | String |
> | Value is greater than input. | Numeric, Date, DateTime |
>= | Value is greater than or equal to input. | Numeric, Date, DateTime |
< | Value is less than input. | Numeric, Date, DateTime |
<= | Value is less than or equal to input. | Numeric, Date, DateTime |
Is null | Value is a missing or unknown value. | Numeric, Date, DateTime, Time, String, Spatial, Boolean |
Is not null | Value is not a missing or unknown value. | Numeric, Date, DateTime, Time, String, Spatial, Boolean |
Comes before (<) | Value sorts to come before input. | String |
Comes after (>) | Value sorts to come after input. | String |
Contains | Value is found in any part of the string. | String |
Does not contain | Value is not found in any part of the string (case sensitive). | String |
Is empty | Value is Null or "". | String |
Is not empty | Value is not Null or "". | String |
Range | Value is a selected start and end date. | Date, DateTime |
Start date and periods after | Value is the selected first date and a set number of periods that follow. | Date, DateTime |
End date and periods before | Value is the selected last date and a set number of periods that precede. | Date, DateTime |
Is true | Value is true. | Boolean |
Is false | Value is not true. | Boolean |
Build a Custom Filter Using Expressions
To build a custom filter condition, enter your expression syntax into the expression editor. There are also many tools to assist with building Custom filter conditions. See Expression Editor Tips.
Expression Editor Tips
These tools are available within the expression editor to assist with building Custom filter conditions.
Press Ctrl + Space to view a list of all functions.
Type a word or phrase to view a matching list of functions.
Type a [ (left bracket) to view a list of variables to use in the expressions. Variables can be...
Data from an incoming connection or a column created in a previous expression.
Values from an Interface tool connected to the Question anchor of a tool with an expression editor when the tool is used in an app or macro. Use the Question anchor when you want to use question values as variables in the expression. See Interface Tools.
Global variables that are defined in workflow configuration. See Constants.
Select the fx icon to search and browse functions. See Functions.
Select the x icon to browse or search columns and constants.
Select the folder icon to browse or search recent and saved expressions.
Save a Custom Filter
Once you have built an expression you can save it for later use. Select the save icon. Enter your expression name and select Save.
Tool Examples
Example 1. Select Rows Using a Basic Comparison
Use the Basic filter to select rows by comparing a column against a static value.
According to the condition of the above Basic filter, rows with a CustomerID value greater than 30 are output to the True anchor.
Rows with a CustomerID value that is not greater than 30 are output to the False anchor.
Example 2. Select Rows With or Without Missing Data
Use the Basic filter to select rows without missing data.
According to the condition of the above Basic filter, rows with a LastName value that is not null (missing) are output to the True anchor.
Rows with a null value in LastName are output to the False anchor.
Example 3. Select Rows Using Date-Time Data
Use the Basic filter to select rows with a relative date.
According to the condition of the above Basic filter, rows with a JoinDate that is less than or equal to today's date are output to the True anchor.
Rows with a date after today's date are output to the False anchor.
Date-Time Filter Tips
The dynamic options, today, tomorrow, and yesterday, update the workflow to that relative date when the workflow is run.
The Start date and periods after or End date and periods before operators allow you to specify a date range by selecting a specific date, a Period type (Days, Months, Weeks, Quarters, or Years), and the Number of periods before or after.
Select Filter only Date data to truncate date-time data to use only the date data.
Example 4. Select Rows Using a Multiple-Column Condition
Use the Custom filter to create a condition that references more than one column to select rows.
According to the condition of the above Custom filter, rows with a JoinDate that is greater than or equal to the FirstPurchaseDate are output to the True anchor.
Rows with a JoinDate that is less than the FirstPurchaseDate are output to the False anchor. In this case, there are no rows that have a JoinDate less than the FirstPurchaseDate.
Example 5. Select Rows Using a Compound Condition
Use the Custom filter to create compound conditions, that is, more than one condition joined by and/or operators.
According to the condition of the above Custom filter, rows where the region is either South or contains the word West, are output to the True anchor.
All other rows are output to the False anchor.
Troubleshooting
Treat numbers with more than 15 digits as strings to prevent loss of precision. You can set the field type to a string using the Select Tool.