Beaker with bubbling liquid

Formula Tool

Version:
Current
Last modified: June 19, 2020

Use Formula to create new columns, update columns, and use one or more expressions to perform a variety of calculations and operations. See Functions for a list of functions to use when building an expression.

You can use the Formula tool to...

  • Apply conditional statements.
  • Convert numbers and strings.
  • Format dates.
  • Extract file paths.
  • Apply financial algorithms or mathematical calculations.
  • Find the minimum and maximum values.
  • Analyze spatial data.
  • Cleanse string data.
  • Perform validation tests on data.

Configure the Tool

In the Configuration window, select an Output Column of data via the Select Column dropdown. You can choose an existing column, or add a new column. To add a new column of data...

  1. Select + Add Column and enter the new column name.
    • Data type: Select a data type, if necessary. See Data Types.
    •  Size: Select to change the size or width of the data for Fixed Decimal, String, or Spatial Object data types.
  2. Select the expression editor to build your expression. Once the workflow is run, the Data Preview box displays the first row of data from the specified column with the expression applied.

Build an Expression

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. See Interface Tools.
    • 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. See Constants.
  • Select the Functions icon ("fx") to search and browse through categories of functions. See Functions.
  • 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 SaveOnce 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 modifying data in related columns or performing similar operations. When modifying unrelated data or performing unrelated functions, using multiple Formula tools assists with workflow troubleshooting. To add an expression, select the "+" icon.

Image showing the Formula tool expression editor with the "Add Expression" icon highlighted.

 

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.

Image showing the Formula tool expression editor with the "Reorder" icon highlighted.

 

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.

Was This Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support.