Skip to main content

Liquid drops falling into a beaker. Multi-Row Formula Tool

One Tool Example

Multi-Row Formula has a One Tool Example. Go to Sample Workflows to learn how to access this and many other examples directly in Alteryx Designer.

Use Multi-Row Formula to take the concept of the Formula tool a step further. This tool allows you to use row data as part of the formula creation and is useful for parsing complex data, and creating running totals, averages, percentages, and other mathematical calculations.

The Multi-Row Formula tool can only update one field per tool instance. If you would like to update multiple fields, you need to add a Multi-Row Formula tool for each field to be updated.

Configure the Tool

Use the tool's Configuration window to customize these settings:

  1. Choose to Update Existing Field or Create New Field. If creating a new field, enter a name, and select the Type and Size. Use Size (the size of the data type) to update manually for Fixed Decimal, String, Date-time, Time, or Spatial Object data types.

  2. Use the Num Rows field to set the row variables that display as in the expression editor. When set to 1, these variables display

    • Row - 1: The row before the active row.

    • Row + 0: This is the Active Row. The active row is the row the result goes into.

    • Row + 1: The row after the active row.

  3. When the Active row is the first row, you need to specify the Values for Rows That Don't Exist field.

    • Null: Null values are applied to nonexistent rows.

    • 0 or Empty: The values are set to 0 or left empty (default).

    • Set to Values of Closest Valid Row: The values of the closest valid row are set to the nonexistent rows.

  4. Group by (Optional): Either select fields individually or use the All or Clear buttons.

  5. Build the expression that updates the chosen field. Go to Expression Editor for details.

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.