1. Applying Formulas in Alteryx
Now that you've experienced building expressions in the Filter tool, let's dive deeper into formulas in Alteryx!
2. Various formulas are available
The Formula tool provides a wealth of ready-to-use formulas in various categories, such as Conditional, DateTime, Finance, String, and much more.
3. Formula expression builder
The Expression Builder area in the Formula tool is similar to that in the Filter tool. This allows you to write custom expressions or modify existing formulas. When the formula syntax is written correctly, the expression will color-code each part. Your data preview will show the formula result based on the first record in the dataset.
4. Data types are important
Data types play an important role in writing formula syntax correctly. With expressions, string criteria require quotes, and numeric criteria do not. It is also essential that the formula category uses the correct data type.
5. String formula examples
One example of string formulas is PadLeft, where you can add a character to the left of an existing field. This formula is useful when adding leading zeroes to a Product SKU or ID.
6. String formula examples
Replace can be used to replace characters with string text, such as in this example of replacing "Co. with "Company".
7. Flagging with the Contains formula
The String formula "Contains" is useful for flagging data and outputs a "0" or "-1" as a result. The "-1" acts as a flag if the string contains certain characters or text specified by the user. In this example, the Stage field contains the word "Final".
8. Length-based vs. Position-based string formulas
String formulas can also be length-based or position-based. This affects how the number of characters or words is counted when writing expressions.
9. Length-based formulas
A length-based formula begins by counting the characters in a string with the number 1. One formula that utilizes the length count is "Left". In this example, the three characters on the left, 1
10. Length-based formulas
2,
11. Length-based formulas
3, are "ALT" in ALTERYX.
12. Position based formulas
A position-based, or "nth or 0-based" formula begins counting at zero. GetWord is a string formula that uses a position-based count. In this example, we begin counting the words with 0,
13. Position based formulas
1,
14. Position based formulas
2,
15. Position based formulas
3. Here, the word "Preparation" is in position 1, so the formula would return "Preparation" as a result.
16. Numeric formula examples
Numeric formulas can be applied to numeric data type fields, such as addition and subtraction, and utilized in some Finance and Math formula categories. Numeric formulas are especially useful in calculating percentages, such as percent of sales.
17. Fixed decimals
A fixed decimal option is available when working with numeric data and provides a value for the Precision and the Scale. What is Precision versus Scale though?
18. Fixed decimals
Precision is the length of characters for the entire number.
19. Fixed decimals
All digits before the decimal point,
20. Fixed decimals
AND all digits after the decimal point.
21. Fixed decimals
A space for the decimal point,
22. Fixed decimals
and a space for the negative sign.
23. Fixed decimals
Here, the Precision would be 10.
24. Fixed decimals
Scale is the number of digits after the decimal point.
25. Fixed decimals
Here, the Scale is "2".
26. Updating fields and creating new ones
Formulas can be applied to existing fields within a dataset. Note that the data type of the existing field cannot be changed. You can also create new fields and apply formulas simultaneously. Use the "Add Column" option under "Select Column" and type the new field name. When creating new fields, the data type can be manually set.
27. Stacking formulas in the Formula tool
Formulas can be "stacked" by clicking the plus sign to add a new formula, allowing multiple formulas within one tool. All new fields created can be used in subsequent formulas within the same tool,and are located in the "New Fields" area of the Columns and Constants field list.
28. Let's practice!
Now that we've explored various formulas, let's apply our knowledge!