Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music

Tool Mastery | Filter

16 - Nebula
16 - Nebula
Created
Filter.png

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:

 

1.png

 

You can see that 3 records are True (T) and 2 are False (F) for a dummy set of data:

 

2.png

 

  • 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:

 

3.png

 

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:

 

4.png

 

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:

 

5.png

 

  • 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")

 

6.png

 

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:

  

7.png

 

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:

 

8.png

 

Here are the results for USA:

 

 9.png

 

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 community@alteryx.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.

Attachments
Comments
6 - Meteoroid

Is there a limit to the number of characters that can be typed into the custom criteria of the filter tool?

8 - Asteroid

Wow.  The suggestion that we can use the Filter Tool as a detour to perform more complex calculations on fields is powerful advise.  Thanks!!

 

 

7 - Meteor

Perhaps my question will be answered later in a different section but will ask anyway. I had a bit of an issue finding the correct mathematical expression to input in the custom option filtering.

 

In the practice exercise it prompts, "Find all stores where the first three months sold less than 25% of their yearly total"

 

For which the custom expression solution is  "([January]+[February]+[March])/[Total ]<.25 ". I had it partially correct, and had to look at the solution to correct it. Is there any type of reference repository of mathematical expressions that can be used for these types of calculations? I saw that the Formula Tool had such repository. 

 

 

6 - Meteoroid

"

Perhaps my question will be answered later in a different section but will ask anyway. I had a bit of an issue finding the correct mathematical expression to input in the custom option filtering.

 

In the practice exercise it prompts, "Find all stores where the first three months sold less than 25% of their yearly total"

 

For which the custom expression solution is  "([January]+[February]+[March])/[Total ]<.25 ". I had it partially correct, and had to look at the solution to correct it. Is there any type of reference repository of mathematical expressions that can be used for these types of calculations? I saw that the Formula Tool had such repository. "

 

For this you just need to change "([January]+[February]+[March])/[Total ]<.25 " to "([January]+[February]+[March]) < [Total ] * .25 " given the fact that is less than 25% of the yearly total.

Hope this help. 🙂

8 - Asteroid

Cool showcases, thanks a lot for sharing.

7 - Meteor

Yes,There can be many approaches to "Find all stores where the first three months sold less than 25% of their yearly total" . I tried this formula  ([January]+ [February] + [March])< [Total ]/4, as number of months are not variable in a year we can apply [Total ]/4.

 

I am really excited to see so a variety of solutions in the community.

Hope this helps.

Spoiler
Screen-04-07-2020_150725.png

 

 

5 - Atom

Awesome tool ,Thank you  !! 

5 - Atom

Thank you for showing how to remove fields with a single null value. That and filtering out certain words is a major time-saver when dealing with large Excel datasets where deleting tens of thousands of rows takes a lot of time and memory.