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 FormulaTool on our way to mastering the Alteryx Designer:
Data blending, transformation, and cleansing..oh my! Whether you're looking to apply a mathematical formula to your numeric data, perform string operations on your textfields (like removing unwanted characters), or aggregate your spatial data (among many other things!), the Formula Tool is the place to start. With the examples provided below, you should be on your way to harnessing the many functions of the Formula Tool:
It doesn't stop there, either. The Formula Tool and it's Multi-Row or Multi-Field counterparts are a one-stop-shop for manipulating your data sets - and there are plenty more functions to choose from. If you're working with strings specifically, our Live Training Working with Strings in Alteryx might help. If you're cleaning your data set, be sure to also check out the Data Cleansing Tool (in action here) first to more easily replace nulls in your data, modify case, remove unwanted characters across any fields you select, or trim whitespace.
To help you get started, here is a quick tour of the Formula tool's interface.
The output column is specified in the top-left corner of an expression. You can modify an existing field by selecting an existing field name from the drop-down menu, or create a new field by typing in a new field name.
The default data type for a new field is V_WString with max length. You can change the data type of a new field in the drop-down menu under the expression editor.
Variables, Functions and Saved Expression Menus
You can find the variables, functions, and saved expression menus under the buttons on the left-side of the expression window.
Expressions in a Formula tool are executed sequentially in order. To change the order of an expression (when minimized, or expanded), move the mouse over the handle:
Click and drag the expression to the new location (the blue line indicates the new position):
The expressions are now reordered. Note that expressions are executed sequentially in the formula tool. Also, only one expression can be moved at a time.
Removing an Expression
To remove an expression, expand the expression by clicking on the caret next to the expression:
Then click on the trash can to remove the expression:
Adding Expressions to the Formula tool
Click on the expression window, and press ctrl+space to see a full list of all the functions, constants, and incoming fields:
The Formula Tool will display a list of the functions, columns, and constants that are available:
Typing will automatically filter the results. NOTE: typing ‘dat’ returns functions that start with ‘dat’ as well as functions that contain ‘dat':
The new autocomplete functionality finds functions related to what has been typed. For example, if a user wanted to use a function to calculate the average, and started typing ‘av…’, the autocomplete function shows matching functions:
Press enter to complete the formula:
There are three ways to add columns to your expression:
1. Type the column names
2. Use the Columns or Constants buttons:
- To add columns, click on the Columns or Constants buttons (1), and
- Search for columns by typing the column name in the search window (2) and then double-clicking on the column to insert it into the formula (3)
- Double-clicking on the column or constant name to insert it into the formula (3)
3. Inserting a left bracket ([) in the formula will display a list of columns and constants that may be inserted into the formula:
The completed formula:
The Data Preview window will be empty until the workflow is run once (data preview will be available if the workflow has been run for one tool upstream previously):
After running the workflow:
Subsequent formulas will preview the data without running the workflow:
If the formula contains an error, the Data Preview will show the error:
Formula display size:
Pressing CTRL + increases the size of the formula:
Pressing CTRL - decreases the size of the formula:
By now, you should have expert-level proficiency with the Formula 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 email@example.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.
Stay tuned with our latest posts every Tool Tuesday by following Alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.
Click on the corresponding language link below to access this article in another language -