Introduction
Data preparation often involves repetitive tasks like applying formulas across multiple fields or analysing relationships between records. Alteryx offers two versatile tools, Multi-Field Formula and Multi-Row Formula, to simplify these processes. This article will discuss both tools functionality, use cases, and key differences.
Multi-Field Formula Tool
The Multi-Field Formula tool allows users to apply a single formula across multiple fields simultaneously, making it ideal for scenarios where consistent transformations are required across columns.
The Multi-Field Formula tool has one input and one output, processing data from a single source and producing a single transformed output.
When opening the configuration pane (Figure 1), you must select the data type (Figure 1A) you intend to work on to ensure the formula is applied only to compatible fields.
Figure 1B shows the field selection check boxes. At least one field must be selected from the available fields.
The tool can be configured to either modify the existing columns or create new columns with a specified prefix or suffix (Figure 1C-D).
The output data type can either remain unchanged or be converted to a new data type (Figure 1E). FixedDecimal, String, WString, V_String, V_WString, Time, DateTime, and SpatialObj all allow the output size to be modified.
Figure 1F shows the expression editor that can be used to formulate expressions, much like the standard Formula tool in Alteryx. The key difference are the following three variables:
- _CurrentField_: contains the selected field(s).
- _CurrentFieldName_: contains the name(s) of the selected field(s).
- _CurrentFieldType_: contains the data type of the selected field(s).
Figure 1: Annotated depiction of the Multi-Field Formula tools configuration window. A) Select data types; B) Select columns; C) Copy output to new fields or transform original D) Add a prefix or suffix for new fields; E) Select output data type. F) Expression editor.
Multi-Row Formula Tool
The Multi-Row Formula tool allows users to create calculations using data from the preceding or subsequent rows. This can be particularly useful for creating running totals and parsing complex data.
Similar to the Multi-Field Formula tool, the Multi-Row Formula tool also has a single input and output. However, within the Multi-Row Formulas tool configuration windows there are some notable differences.
Figure 2 shows the Multi-Field Formula tools configuration window. Initially there is the option to either update the existing field or to create a new field for the output (Figure 2A). If the new field option is selected, then the field name and its data type can be specified (Figure 2B).
In Figure 2C the number of rows can be selected to work on. If more rows are selected than exist, the drop-down options provide options to fill them in with either NULL, 0 or Empty, or to Set to Values of Closest Valid Row.
Optionally, the data can be grouped by one or more fields prior to performing the calculations (Figure 2D).
Finally, the expression editor can be used to formulate the expression, like the Formula tool and the Multi-Field Formula tool. However, row specific variables will be available. For example, if one row has been selected as the Num Rows, then the following variables will be available:
- Row – 1: the row before the current row.
- Row + 0: the current row.
- Row + 1: the next row.
Figure 2: Annotated depiction of the Multi-Row Formula tools configuration window. A) Update existing field or create a new field; B) New Field name and data type; C) Number of rows to work with; D) Fields to group by; E) Expression editor.
Conclusion
As it can be seen, both tools can be quite powerful, providing additional flexibility over the standard Formula tool in Alteryx. Knowing how and when to use each tool can significantly improve the efficiency and effectiveness of data preparation and analysis workflows. To learn more, work through both tools One Tool Examples and build some workflows of your own.