1. Filtering and Using Expressions
Welcome back! We will now focus on filtering and using expressions, which are essential aspects of preparing data for analysis. This knowledge will enable you to create subsets of data and gain new insights!
2. Filtering Data
Filtering is the process of reducing a dataset by removing some data in order to focus on a subset of data. The subset is often based on specific criteria, such as “Sale Price is greater than or equal to $100,000”. Here you can see the results of the filter being applied.
3. The concept of filtering is like . . .
You can think of filtering as sculpting a statue, removing extraneous material to reveal the art underneath.
4. Filtering in Alteryx
In the Favorites toolset as well as the Preparation toolset, the Filter tool enables you to perform both Basic and Custom filters. Basic filters consist of one field with an operator and/or specific criteria applied, such as Sales greater than or equals to 100000,
5. Filtering in Alteryx
Employee ID is not null,
6. Filtering in Alteryx
or Product contains "Pizza".
7. Filtering in Alteryx
Custom filters allow you to utilize multiple criteria and write expressions to create a subset of data. For example, Sales greater than or equal to 100000 and Price greater than zero,
8. Filtering in Alteryx
Name equals Jane or Name equals John,
9. Filtering in Alteryx
or even a range between dates, such as dates between January 1st, 2022 and January 1st, 2024.
10. Filter tool outputs
The Filter tool features two output anchors: True and False. True means the criteria in the expression are met, and all records that meet these requirements will be output.
11. Filter tool outputs
False will show all records that do not meet the specified criteria.
12. AND and OR
A Custom filter can use Boolean logic terms such as AND and OR within the criteria expression. These terms work in different ways: AND signifies that BOTH criteria must be met. For example, Shape equals Triangle AND Letter equals B. OR means that EITHER criteria must be met, where the Shape OR the Letter criteria is met. Let's look at a couple of examples with this "dataset".
13. AND statement
In this AND statement, both shape criteria equals a triangle, and the letter equals B is met.
14. OR statement
When changed to an OR statement, either the shape equals a triangle, or the letter equals B. The result is very different than the AND statement!
15. Expression syntax
It is important to know the data type of the fields you are working with since the syntax of the expression will depend on whether the data type is a string, numeric, or other type. String criteria require quotes, and single or double quotes can be utilized. Shown here, 'January' needs quotes in the expression.
16. Expression syntax
Numeric data types do not use quotes, so 100 in this expression is not quoted.
17. Expression syntax
An exclamation mark before the operator means "not", such as the Product field is not empty.
18. Expression syntax
And be sure to check the data type, even if the field contains a number. In this case, the Length field is a string, so the zero requires quotes for the expression to function properly.
19. Field syntax in custom expressions
Note the field must be included in each part of the statement when writing Custom expressions. This is true even when you are writing expressions for the same field. Especially when working with dates, it is important to remember to add the field name to the second date criterion when writing expressions to find records between specific date ranges.
20. Formulas in the Filter tool
The Filter tool has many formulas immediately available for use when writing expressions. Click the Fx or Functions button at the top left of the expression builder and view the extensive list of categories. The formulas will be explored further in the next chapter, showcasing the Formula tool.
21. Let's practice!
By the end of this chapter, you will have discovered various techniques in filtering and creating subsets of data. Let's get started in applying filters to our data!