community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More

Tool Mastery | Formula

Sr. Instructional Designer
Sr. Instructional Designer
Created on
Formula.png

This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Formula Tool on our way to mastering the Alteryx Designer:

 

Data blending, transformation, and cleansing..oh my! Whether you're looking to apply a mathematical formula to your numeric data, perform string operations on your text fields (like removing unwanted characters), or aggregate your spatial data (among many other things!), the Formula Tool is the place to start.  With the examples provided below, you should be on your way to harnessing the many functions of the Formula Tool:

 

 

It doesn't stop there, either. The Formula Tool and it's Multi-Row or Multi-Field counterparts are a one-stop-shop for manipulating your data sets - and there are plenty more functions to choose from. If you're working with strings specifically, our Live Training Working with Strings in Alteryx might help. If you're cleaning your data set, be sure to also check out the Data Cleansing Tool (in action here) first to more easily replace nulls in your data, modify case, remove unwanted characters across any fields you select, or trim whitespace.

 

To help you get started, here is a quick tour of the Formula tool's interface.

 

Output Column

 

The output column is specified in the top-left corner of an expression. You can modify an existing field by selecting an existing field name from the drop-down menu, or create a new field by typing in a new field name.

 

2019-03-01_11-22-37.png

 

Data Type

 

The default data type for a new field is V_WString with max length. You can change the data type of a new field in the drop-down menu under the expression editor.

 

2019-03-01_11-27-13.png

 

 

Variables, Functions and Saved Expression Menus

 

You can find the variables, functions, and saved expression menus under the buttons on the left-side of the expression window. 

 

 

2019-03-01_11-29-41.png

 

 

Expression Order

 

Expressions in a Formula tool are executed sequentially in order. To change the order of an expression (when minimized, or expanded), move the mouse over the handle:

 

6.jpg

 

 

 

 

 

 

 

Click and drag the expression to the new location (the blue line indicates the new position):

 

7.jpg

 

The expressions are now reordered. Note that expressions are executed sequentially in the formula tool. Also, only one expression can be moved at a time. 

 

8.jpg

 

Removing an Expression

 

To remove an expression, expand the expression by clicking on the caret next to the expression:

 

10.jpg

 

Then click on the trash can to remove the expression:

 

11.jpg

 

Adding Expressions to the Formula tool

 

Click on the expression window, and press ctrl+space to see a full list of all the functions, constants, and incoming fields:

 

2019-03-01_11-16-09.png

 

The Formula Tool will display a list of the functions, columns, and constants that are available:

 

13.jpg

 

Typing will automatically filter the results.  NOTE:  typing ‘dat’ returns functions that start with ‘dat’ as well as functions that contain ‘dat':

 

14.jpg

Autocomplete:

 

The new autocomplete functionality finds functions related to what has been typed.  For example, if a user wanted to use a function to calculate the average, and started typing ‘av…’, the autocomplete function shows matching functions:

 

15.jpg

 Press enter to complete the formula:

 

16.jpg

 

There are three ways to add columns to your expression:

 

1.  Type the column names

2.  Use the Columns or Constants buttons:

  • To add columns, click on the Columns or Constants buttons (1), and
  • Search for columns by typing the column name in the search window (2) and then double-clicking on the column to insert it into the formula (3)
  • Double-clicking on the column or constant name to insert it into the formula (3)

17.jpg

 

3.  Inserting a left bracket ([) in the formula will display a list of columns and constants that may be inserted into the formula:

 

18.jpg

 

The completed formula:

 

19.jpg

 

Data Preview:

The Data Preview window will be empty until the workflow is run once (data preview will be available if the workflow has been run for one tool upstream previously):

20.jpg

 

After running the workflow:

 

21.jpg

 

Subsequent formulas will preview the data without running the workflow:

 

22.jpg

 

If the formula contains an error, the Data Preview will show the error:

 

23.jpg

 

Formula display size:

 

Pressing CTRL + increases the size of the formula:

 

24.jpg

 

Pressing CTRL - decreases the size of the formula:

 

25.jpg

 

By now, you should have expert-level proficiency with the Formula Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.

  

Stay tuned with our latest posts every Tool Tuesday by following Alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.

Comments
Asteroid

Is there a way to put some comments inside the expression box? I have formula tools with multiple expressions, and putting a little explanation next to each one would help me keep track of what each one is supposed to do.

Sr. Community Content Manager
Sr. Community Content Manager

@MichalK yes!

https://help.alteryx.com/current/Reference/Functions.htm?Highlight=comment

/*Comment*/: Block Comment: Allows you to add comments to an expression editor (within the expression line) without interfering with the expression.

//Comment: Single Line Comment: Allows you to add comments to an expression editor without interfering with the expression.