Skip to main content

Functions

Use functions to build expressions that perform a variety of calculations and operations.

These function types are available to help you transform your data. The type of data determines the functions you can use. Go to Data Types for more information.

Custom Formula Functions

If you use custom formula functions and your custom function has the same name as an existing Alteryx function (or a newly-introduced Alteryx function), you might encounter unexpected results and conflicts.Build Custom Formula Functions

To resolve this, rename your custom function (or add a prefix/suffix) to differentiate it from base-installed Designer functions.

Types of Functions

  • Conditional: Conditional functions allow you to perform an action or calculation using an IF statement. Learn more in the Conditional Functions article.

  • Conversion: Conversion functions convert numbers to strings or strings to numbers Learn more in the Conversion Functions article.

  • DateTime: DateTime functions allow you to perform an action or calculation on a date and time value. Learn more in the DateTime Functions article.

  • File: A file function builds file paths, checks to see if a file exists, or extracts a part of a file path. Learn more in the File Functions article.

  • Finance: A finance function applies financial algorithms or mathematical calculations. Learn more in the Finance Functions article.

  • Math: A math function performs mathematical calculations. Learn more in the Math Functions article.

  • Math: Bitwise: A bitwise function operates on one or more bit patterns or binary numerals at the level of their individual bits. Use a bitwise function to manipulate values for comparisons and calculations. Learn more in the Math: Bitwise Functions article.

  • Min/Max: A minimum or maximum function finds the smallest and largest value of a set of values. Learn more in the Min/Max Functions article.

  • Operators: An operator is a character that represents an action. Use an arithmetic operator to perform mathematical calculations or a Boolean operator to work with true/false values. Learn more in the Operators article.

  • Spatial: A spatial function builds spatial objects, analyzes spatial data, and returns metrics from spatial fields. Learn more in the Spatial Functions article.

  • Specialized: These functions perform a variety of specialized actions and can be used with all data types. Learn more in the Specialized Functions article.

  • String: A string function performs operations on text data. Use a string function to cleanse data, convert data to a different format or case, compute metrics about the data, or perform other manipulations. Learn more in the String Functions article.

  • Test: A test function performs data comparisons. Use a test function to identify the data type of a value, or determine if a value exists. Learn more in the Test Functions article.

Expression Editor

You can build functions with the expression editor.

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.

Once you specify an Output Column, use any of these methods to build an expression:

  • Enter the function directly in the expression editor.

  • Select the Columns and Constants button to browse through variables that can be used in the expression. Select the variable to add it to the expression editor.

  • 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.

Syntax Highlighting

As you edit the expression, the editor applies a color-coding scheme to its various parts to make the parts of the expression easy to distinguish. For example, functions are gold, variables are fuchsia, operators are magenta, and strings are turquoise, a blue-green.

Functions

Select Functions to browse through categories of functions. Select the function to add it to the expression editor.

Columns and Constants

Select Columns and Constants to browse data from incoming connections and global variables.

  • Existing 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.

Recent and Saved Expressions

Select Recent and Saved Expressions to browse through recent and saved expressions. Select the expression to add it to the expression editor.

Save an Expression

  1. With an expression in the expression editor, select the Save Expression (disk) icon.

  2. Enter a name for the expression and select Save.

These tools include an expression editor. Tools that use an expression editor have an additional Input anchor and Question anchor when the tool is used in an app or macro workflow. Use the Interface tools to connect to a Question anchor when you want question values to be used as variables in the expression. Go to Interface Tools for more information.

Action Tool Icon
Condition Tool Icon
Error Message Tool Icon
Dynamic Rename tool
Filter Tool Icon
Beaker with bubbling liquid
Box with rows, a plus sign and an arrow pointing downward.
Beaker with liquid and stir-stick.
Liquid drops falling into a beaker.
Orange polygon with white table with multiple rows.
Message tool
Test Tool

Numeric Data in Formula Functions

Warning

Be careful when you use Fixed Decimals in the Formula tool and when you convert Fixed Decimals to Floats or Doubles. In the Formula tool, Fixed Decimals are implicitly converted into Doubles. The maximum precision for the Double data type is 15 digits. For the Float data type, it's 7 digits. If you are converting Fixed Decimals, you need to expect to lose all the data that can’t fit into the type you are converting to.