The formula tool is one of the blue tools in Alteryx and can be found under the “Preparation” tab. The Formula tool enables you to perform a variety of calculations and operations to create new data columns or update existing columns.
In this example, we are going to update an existing column using the Formula Tool and performing the update using the conditional formula such as the IF/ELSE Conditional Formula.
When you drag and drop the Formula tool onto your canvas, a window pane will show on the left. You can select the column you want to update then you can click on the 'fx' button to view your options and how to write the formula.
In this example, we want to rename the customer segment column in our data.
Using a conditional statement, set 'CORPORATE' to 'CORE', 'HOME OFFICE' to 'HOME', and anything else to 'OTHER'
The following is how we accomplish the update. After dragging and dropping the Formula Tool onto the canvas, select Customer Segment from the Output Column area. Then input the formula below:
IF [Customer Segment]= 'CORPORATE' THEN 'CORE'
ELSEIF [Customer Segment]= 'HOME OFFICE' THEN 'HOME'
ELSE 'OTHER'
ENDIF
The above follows the suggested syntax; IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF. Click on RUN to run the statement, check if the output updated the values.
Another example of the Formula Tool is that it can combine two columns to create a new column. This is not the IF/ELSE Conditional Formula, but another use case of the Formula Tool.
eg - Create a new field called 'Full Name'. In the new field, concatenate 'First Name' and 'Last Name' together.
After dragging and dropping the Formula Tool, in the Output Column, click on Add Column and rename it. In the Formula field, insert your formula - [First Name]+ ' ' +[Last Name]. You can see the results in the Data Preview field. Click Run. You can click the 'Input' icon and see where and what has been added after you click the 'Output' icon. There should be a Full Name column which has the first name and last name as values.
These are only the most basic uses of the formula tool, it has A LOT more that it can do!
