Alteryx formula tool: interface and basic use

by Skomantas Tamulaitis

The formula tool in Alteryx is very powerful and can execute a variety of different functions including, but not limited to, cleaning data, merging columns, changing data types, and working with stings. Today I will provide a basic overview of the tool and how it works. I will be using US Sample Superstore dataset.

Formula Tool Interface

The formula tool is one of the blue tools in Alteryx and can be found under the “Preparation” tab. In the upper left corner of the formula tool you have “Output Column” which allows you to specify the column you want the outcome of the formula to be saved to. You can can choose to overwrite an existing column or create a new one. Here I selected “+ Add Column” and called it “Profit Ratio”.

In the upper right corner is the “Data Preview” section which allows you to preview the outcome of the formula.

At the bottom of the window we have “Data type” section. Here you can select what is going to be the data type of your formula's outcome. Next to it is the “Size” section where you can restrict the size of the outcome in bits.

On the bottom left we have the little plus icon which allows you to write a new formula within the same formula tool. This way you do not need to drag a new formula tool into the workflow. However, keep in mind that someone who is not familiar with the workflow might think that your formula tool only contains one function. That is why I usually add new formula tools for each formula, it is simply easier to follow the workflow logic.

On the left side there is the fx icon, which provides you with all the formulas that can be used in this tool, there is more than 100!

Basic Uses of the Formula Tool

Arithmetic Operations

Formula tool can be used for basic calculations. In the images above you can see a "Profit Ratio" calculation where I simply wrote the name of the profit column in square brackets, followed it up with a division sign, and wrote the name of the sales column in square brackets. Voila! It is that simple to do arithmetic operations with data columns!

Merging String Columns

You can also merge string columns with formula tool. Let’s type in this formula: [Country/Region] + "-" + [Region]. Just like in previous example, the columns are represented by whatever is in the square brackets, but in this case we also added a delimiter between the quotation marks (it can be a backspace, or any other symbol). Delimiter will appear between the values from both columns.

Press run and that is it! You have merged two columns and separated them by the symbol you put in the quotation marks. Here is how the two columns looked before, and what the new “Country – Region” column looks like after.


These are only the most basic uses of the formula tool, it has A LOT more to offer, take a look!

Avatar

Skomantas Tamulaitis