Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Tool Mastery

Explore a diverse compilation of articles that take an in-depth look at Designer tools.
Become a Tool Master

Learn how you can share your expertise with the Community

LEARN MORE

Tool Mastery | Filter

patrick_digan
17 - Castor
17 - Castor
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.

Additional Information
Click on the corresponding language link below to access this article in another language -
Portuguese
Spanish
French

 

Attachments
Comments
jforster
6 - Meteoroid

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

llytle
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!!

 

 

lmalbertorio
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. 

 

 

danlelps
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. 🙂

AliAS2020
8 - Asteroid

Cool showcases, thanks a lot for sharing.

Danish409
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

 

 

Mouhsine_Bella
5 - Atom

Awesome tool ,Thank you  !! 

jrsupp
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.

AnthonyFGCR
5 - Atom

Good example, clear design, a very good intuitive model and explanations.

Tripti_Lal
8 - Asteroid

How to create a drop down?
I have inserted the tool and but I want to manually add the list values. I want to add values similar to 'List values' in Data Validation in Excel.

 

I am unable to add values manually and cannot open the drop down arrow for list to appear.

 

Kindly guide! Thanks!

mjsnyder
7 - Meteor

The filter tool is a great tool to narrow down the information you need.

rdeshpande
7 - Meteor

Thank you for explaining the filter tool in detail. The detour advice is precious. Thanks again.

ebrown03
5 - Atom

Is there a simpler way to accomplish the same calculations from the last example? I feel like there has to be a different way to create the workflow in the second to last picture, is there an elseif option that would shrink that down a bit?

Thank you for the detailed explanation. 

Tyrane782001
7 - Meteor

So the filter tool can be used to place formulas like in excel? to filter the information you want to see or know

Ankitec
5 - Atom

Thank you for detail explanation!

VTU15986
5 - Atom

tq sir

mvtejano
7 - Meteor

Interesting and very helpful. Thank you.

Gayitri420
7 - Meteor

Cool showcases, thanks a lot for sharing.

srilakshmi123
7 - Meteor

hhtps://community.altery.com/t5/alteryx-community-english/ct-p/externa

Anitha1289
7 - Meteor

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

Shyam_amarapu_2002
7 - Meteor

5-atom

GuthurthiRaju
6 - Meteoroid

cool

nadiralam
7 - Meteor

Interesting and very helpful. Thank you.

Jagrat_sahni
6 - Meteoroid

hello

Mohitku123
6 - Meteoroid

IT WAS VERY GOOD TOOL TO LEARN.

 

 

Aditya_88
5 - Atom

Great

SeanBain1965
5 - Atom

I found in the last exercise that you could either use [Total]/4 or [Total]*.25. I also found you didn't need to add brackets to enclose the months as it made no difference as the '<' does not affect the way the math runs - [January]+[February]+[March] <  [Total ]*.25 or [Total ]/4. its not using Bodmas or Pedmas.

Mgaurav2grt
7 - Meteor

awesome

smitacarvalho
6 - Meteoroid

Can someone help with the Filter Try It Exercise? I am stuck on finding store names that do not contain "pet" anywhere in their name. I used the function !Contains([Name],"pet" 

 

I've tried every combination of Pets, Pet, etc. and still can't get it to work. Any ideas?

 

smitacarvalho_0-1679442867300.png

smitacarvalho_1-1679443092836.png

smitacarvalho_2-1679443135392.png

 

 

lillyrowling18
5 - Atom

The True Side of the filter displays the three records that contain either bacon or apple in the Food Idiom category and come from Source 1 or 2.

 

It was really helpful, thanks for sharing.