Tool Mastery | Formula

[ChristineB]

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:

• Use DateTime functions when time is of the essence
• Write conditional statements
• Apply spatial functions instead of using the spatial tool palette
• Leverage the Modulo (mod) function for reshapes and calculations
• Round numeric values
• Remove leading zeroes from your data
• Convert numerics from scientific (E) notation

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.

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.

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.

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:

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

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.

Removing an Expression

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

Then click on the trash can to remove the expression:

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:

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

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

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:

Press enter to complete the formula:

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)

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

The completed formula:

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):

After running the workflow:

Subsequent formulas will preview the data without running the workflow:

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

Formula display size:

Pressing CTRL + increases the size of the formula:

Pressing CTRL - decreases the size of the formula:

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 -

• Portuguese
• Spanish
• French
• German
• Japanese

[MichalK]

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]

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

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

[shuman-p]

good and succinct post!

[minty]

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

[adrilarotta]

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]

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

PadLeft([Field1], 8, '0')

[AmrSHOULA]

Excellent!

[MikaelO]

I am gradually learning this. 

[MikaelO]

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]

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

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

[Turnverein]

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]

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

[srilakshmi123]

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

[NeilR]

@srilakshmi123 check out the File Functions.