IF/ELSE Conditional Formula in Alteryx

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.

Formula Tool can be found in the Preparation tab

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.

IF/ELSE Conditional Format

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!

Author:
Carol Mhlanga
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2024 The Information Lab