This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Filter Tool on our way to mastering the Alteryx Designer:
The Filter Tool, which is part of the Preparation tool category , separates your data into 2 output streams, True and False, based on a basic filter or custom expression. It is currently the #5 tool on the Periodic Table. You may want to first complete the interactive lesson on Removing Rows from Your Data.
Use it to...
- Remove Rows with Single Null Value (attached in the v11.3 application Filter.yxwz):
The filter tool has a basic filter which can get you simple answers very quickly. If you wanted to remove all rows where Field2 was null, you can select Field2 from the basic filter and then select is not null:
You can see that 3 records are True (T) and 2 are False (F) for a dummy set of data:
- Filter out Specific Date Ranges(attached in the v11.3 application Filter.yxwz):
The custom filter can be used to create expressions that return a True or False for every row of data coming in. For example, you can have 2 Date Interface Tools , named Start Date and End Date in their respective Annotation configurations, that can be used to let the user filter the data:
Only records that meet both criteria will be true since we used an AND statement between our two expressions. If a user selects ‘2017-01-01’ and ‘2017-01-07’ for Start and End Date respectively, then the T side of the filter will show us all records from that time frame:
In order to test that the filter is working properly, Start and End dates can be entered into the Workflow tab of the Workflow – Configuration panel:
- Filter out certain words contained in cells(attached in the v11.3 application Filter.yxwz):
You can use multiple contains functions separated by an OR operator (“||” or “OR” will both work) and then add another condition with an AND operator (“&&” or “AND” will both work) to filter out rows that meet both criteria:
(Contains([Food Idioms],"bacon") || Contains([Food Idioms],"apple")) && [Source] IN ("Website1","Website2")
The True Side of the filter displays the three records that contain either bacon or apple in the Food Idioms category and come from Source 1 or 2.
- Use a Filter like a Detour Tool (attached in the v11.3 application Filter.yxwz):
Perhaps you have a very manual and unconnected formula you use to forecast sales which depends on the Region, Country and Continent. Instead of a single complicated looking formula tool, you can use filter tools to split out the data (sort of like the detour’s functionality), calculate your projection, and then union the results back together. Of course, you want to be able to have the user select the country they are concerned with as well. The first filter is setup to filter the data down to 1 user selected country:
Notice that the Dropdown is connected directly to the Q on the Filter so that it is now available as part of the Connections from Questions. This allows for easier debugging as test values can be tested in the Workflow tab of the Workflow - Configuration tab.
Now that we have our country selected, we can run it through our set of filters/formulas:
Here are the results for USA:
By now, you should have expert-level proficiency with the Filter Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at email@example.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.
Stay tuned with our latest posts every #ToolTuesday by following @alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.
Click on the corresponding language link below to access this article in another language -