Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Tool Mastery

Explore a diverse compilation of articles that take an in-depth look at Designer tools.
Become a Tool Master

Learn how you can share your expertise with the Community

LEARN MORE

Tool Mastery | Formula

ChristineB
Alteryx Alumni (Retired)
Created
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 FormulaTool 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 textfields (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.

Additional Information

Click on the corresponding language link below to access this article in another language -



Comments
MichalK
8 - 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.

NeilR
Alteryx Alumni (Retired)

@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.

shalinian
5 - Atom

In one of the formula tool, I can see [user.fieldname]. please help me to understand this

shuman-p
7 - Meteor

good and succinct post!

minty
7 - Meteor

This is an awesome post! How do you recommend extracting the file path using the formula tool? 

adrilarotta
8 - Asteroid

Hi guys, 

 

Does anyone knows how to insert a number in the left for a string value, I have several string dates with this format 20072020, some dates are like this 1072020, so when I use DateTime it does not recognize the customized format. I need to add a zero at the left in this cases so the DateTime tool can change the string value to date.

 

Thanks in advance

NeilR
Alteryx Alumni (Retired)

@adrilarotta I believe you're looking for padLeft...

PadLeft([Field1], 8, '0')
AmrSHOULA
7 - Meteor

Excellent!

MikaelO
6 - Meteoroid

I am gradually learning this. 

MikaelO
6 - Meteoroid

HELLLLLPPP Please. i don't want to loose my concentration. i am struggling to concatenate text strings from different columns into one column using the formula tools. please i have tried to add up the two words but i am not satisfy with my work. 

TyroneW
Alteryx Alumni (Retired)

@MikaelO  The plus symbol, otherwise known as the addition operator '+', is also used for string concatenation.

Example: [ColumnName]+[ColumnName]

 

You can also concatenate literal strings as well.

Example: "Hi "+[ColumnName]

 

The spaces around the plus symbol don't impact the results, nor does using single- or double-paired quotes around a literal string. 

Tyrane782001
7 - Meteor

The formula tool is very helpful.. just have exercise to get a clear understanding on all the capabilities

Turnverein
6 - Meteoroid

For the Try It section for this module, can someone explain what is happening with the solution:

getword([Address],CountWords([Address])-1)

What does the - symbol do?

lepome
Alteryx Alumni (Retired)

@Turnverein 
The minus sign allows for conversion from natural numbers to an index starting with 0 rather than 1.

srilakshmi123
7 - Meteor

This is an awesome post! How do you recommend extracting the file path using the formula tool? 

NeilR
Alteryx Alumni (Retired)

@srilakshmi123 check out the File Functions.