Advanced Alteryx Tool Options

by Adam Sultanov

In order to supplement my previous blog post, Navigating the Alteryx Tool Set, which goes over the basic tools at one's disposal, I'm going to add descriptions here about some advanced tools to really supercharge your workflows. These tools require some extra thought and configuration, so it's good to be certain that they are necessary and that the problems you are working on can't be solved with simpler Alteryx tools.

The Generate Rows Tool

The Generate Rows tool can be used to create or modify a data set. It has an optional input anchor which you can omit connecting anything to if you are creating a table from scratch. It has an interesting configuration pane, which includes the option to update an existing field or create a new one, and an interface that approximates a FOR loop in a programming language. There is a starting variable (Initialization Expression), a condition to determine when the loop ends (Condition Expression), and logic concerning what to do after each loop (Loop Expression) where you generally will increment your variable in some way. These fields all accept formulas, which can have varying degrees of complexity. One thing you need to keep in mind is when working with dates in the Loop Expression to use DateTimeAdd() instead of a simple addition operator or else you will generate a parse error.

The Multi-Field Formula Tool

The Multi-Field Formula tool is for when the normal Formula tool simply won't cut it. While the formula tool can be used to update or create new columns one at a time, the Multi-Field Formula tool can be applied to any number of columns. It has a field selection interface, an option for modifying or copying columns, an option to change the data type of the field, and an expression editor. In this editor you have access to the current field along with its name and type, as well as the rest of the fields in your data set. What you need to be careful about is the data type of the columns you select to perform operations on. You can choose fields of mixed types whereas most formulas will only be applicable to a single type, so you can easily generate a type mismatch error. This tool is especially handy when you would have to perform the same operation on many columns, saving you from having to type them out over and over again in the regular Formula tool.

The Multi-Row Formula Tool

The Multi-Row Formula tool is perhaps the most complicated of the tools described here. However, there are a couple scenarios where it can save a lot of time. The configuration pane is quite complex. Just like the normal Formula tool, you can update existing fields or create new ones. You can then choose how many rows you need Alteryx to look forward/back when your expression runs, and what values to use in the case of rows that don't exist (such as if you are referencing cells in a row above but looking at the first row). There is also an optional panel where you can group the results by specific fields. Finally there is an expression editor, where the big difference is the availability of other rows' relative data to you for use in expressions. This is an excellent way to fill in null rows based on existing data, determine the difference between rows, or calculate rolling averages. The Multi-Row Formula tool is also often used in conjunction with transform tools (as covered in my previous post), doing things like preparing data scraped from the web before pivoting the data with the Cross Tab tool.

Tue 28 Nov 2023

Mon 27 Nov 2023

Wed 22 Nov 2023

2 mins read

Mon 16 Oct 2023