Tool Mastery | Filter

[patrick_digan]

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

 

[jforster]

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

[llytle]

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]

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]

"

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]

Cool showcases, thanks a lot for sharing.

[Danish409]

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

[Mouhsine_Bella]

Awesome tool ,Thank you  !! 

[jrsupp]

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]

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

[Tripti_Lal]

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]

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

[rdeshpande]

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

[ebrown03]

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?

[Franklin_Gbologah]

Thank you for the detailed explanation. 

[Tyrane782001]

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

[Ankitec]

Thank you for detail explanation!

[VTU15986]

tq sir

[mvtejano]

Interesting and very helpful. Thank you.

[Gayitri420]

Cool showcases, thanks a lot for sharing.

[srilakshmi123]

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

[Anitha1289]

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]

5-atom

[GuthurthiRaju]

cool

[nadiralam]

Interesting and very helpful. Thank you.

[Jagrat_sahni]

hello

[Mohitku123]

IT WAS VERY GOOD TOOL TO LEARN.

[Aditya_88]

Great

[SeanBain1965]

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]

awesome

[smitacarvalho]

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?

[lillyrowling18]

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.