Formula Tool
Use Formula to create new columns, update columns, and use 1 or more expressions to perform a variety of calculations and operations. Go to Functions for a list of functions to use when building an expression.
Use the Formula tool to:
- Apply conditional statements
- Convert numbers and strings
- Format dates
- Extract file paths
- Apply financial algorithms or mathematical calculations
- Find minimum and maximum values
- Analyze spatial data
- Cleanse string data
- Perform validation tests on data
Configure the Tool
In the Configuration window, you can create multiple expressions. Alteryx assigns an expression ID number to each individual expression in the order it appears in the configuration window. For example, the 1st expression has an ID of 1, the 2nd has an ID of 2, and so on.
Note
Note that if you rearrange your expressions, the IDs don't follow the expressions. For example, if you have 3 expressions and move the 3rd expression to the top, it will now have an ID of 1.
These IDs can be very helpful when you troubleshoot error messages for your formulas since the expression ID is referenced in the error message.
Build an Expression
To build your expression, first, specify an Output Column. The Output Column will contain the results of your expression. You can overwrite an existing column or you can create a new column. Select the Output Column dropdown and choose an existing column or select Add Column and provide a name for your new column.
- Select + Add Column and enter the new column name.
- Data type: Select a Data Types, if necessary.
Important
For FixedDecimal values, there is an implicit conversion to Double in the Formula tool.
- Size: Select to change the size or width of the data for Fixed Decimal, String, or Spatial Object data types.
- Data type: Select a Data Types, if necessary.
- Select the expression editor to build your expression. Once the workflow runs, the Data Preview box displays the 1st row of data from the specified column with the expression applied.
Use any of these methods to build an expression.
- Enter text directly in the expression editor.
- Press Ctrl + Space to view a list of all functions.
- Enter a word or phrase to view a matching list of functions.
- Enter a [ (left bracket) to view a list of variables that you can use in the expression. Variables include...
- Columns: Data from an incoming connection, or from a column created in a previous expression.
- Connections from Questions: Values from an Interface tool connected to the Question anchor of a tool with an expression editor, when the tool is used in an app or macro. Use the Question anchor when you want question values to be used as variables in the expression. Go to Interface Tools for more information.
- Constants: Global variables for a workflow that make it possible to change a value in a single location and have that change apply to the rest of the workflow. Go to Constants for more information.
- Select the Functions icon ("fx") to search and browse through categories of functions. Go to Functions for more information.
- Select the Columns and Constants icon ("x") to search and browse through incoming or newly created columns and constants.
- Select the Recent and Saved Expressions icon (folder icon) to search and browse through recent and saved expressions.
- With an expression in the expression editor, select the Save Expression (disc) icon, enter a name, and select Save. Once an expression has been built, it can be saved for use at a later time.
Add an Additional Expression
It is helpful to add multiple expressions to a single Formula tool when you modify data in related columns or perform similar operations. When you modify unrelated data or perform unrelated functions, using multiple Formula tools assists with workflow troubleshooting. To add an expression, select the "+" icon.
Reorder an Expression
The sequence in which expressions are applied affects the results when multiple expressions modify the same data. To reorder an expression, select and hold the reorder icon (to the left of the expression editor), and drag the expression up or down.
Please note that when you reorder expressions, their IDs are reassigned. Alteryx assigns an expression ID number to each individual expression in the order it appears in the configuration window.
Expand and Collapse Expressions
As you work on your expressions, the Formula tool configuration window can get a little crowded, especially if you have many individual expressions.
You can use the expand and collapse icons to show and hide individual expressions or to show and hide all expressions at once.
- Select the ">" icon associated with a specific expression ID to show or hide that expression.
- Select the ">" icon at the top of the expression editor to show or hide all expressions at once.
Please note that each time you access the Formula tool Configuration window, the first expression is expanded and all subsequent expressions are collapsed.
Additional Input Anchor
Because this tool includes an expression editor, an additional input anchor displays when the tool is used in an app or macro workflow. Use the Interface tools to connect to a Question anchor. See Interface Tools.