Chapter 4. Writing Fast and Accurate Expressions
There are various tools in Alteryx that allow for a broad variety of calculations. You will utilize multiple expressions to create new data columns, update existing columns, and reference data to qualifying rows. You will use more rigorous expressions to maximize the potential of the Formula, Multi-Row Formula, and Multi-Field Formula tools. The goal is to gain a solid foundation in the different types of Formula tools so you can write powerful expressions.
This chapter will cover the following topics:
- Formula
- Multi-Row Formula
- Multi-Field Formula
The Formula tool is where you can write a variety of calculations and operations. The ability to perform calculations, such as if/else conditional formulas, can be quickly used to create a new column or modify an existing column. More than one calculation can be written within the Formula tool rather than creating a formula for each tool. This is quite helpful when creating a calculation for a new column and finding out you want to modify the new column immediately after creating it. The best part of it all is that after writing the expressions they can be saved!
This is great when the same calculation, or one similar to it, needs to be written in the same workflow downstream, or in a different workflow. Alteryx will save the expression, saving you time from writing it out again, and providing a reminding thought on how it was created. We will go through 15 expressions that will provide you with a solid foundation in writing expressions all in one tool, the Formula tool.
In this section...
You went through some great use cases for the Formula tool, and are now probably wondering how you can update multiple rows based on an expression. The Multi-Row Formula tool accomplishes just that by writing expressions to references preceding and/or following rows. This can be done by creating a new field or modifying existing fields.
In this section, we will use the same workflow from the last section; the U.S. Chronic Disease Indicators workflow. Let's go through a couple of expressions to understand how helpful the Multi-Row Formula is.
Expression #1: Update the existing field DatavalueFootnote where, if the current row is null, then fill in the null with the previous row using the same field.
Step 1: Select the Multi-Row Formula tool from the Preparation tool palette and drag it onto the canvas. Connect the Multi-Row Formula tool to the Formula tool.
Step 2: Select Update Existing Field, and from the dropdown select DatavalueFootnote.
Step 3: Write the following expression...
Multi-Field Formula allows for multiple fields to be updated at once through a single function. We will use the same workflow from the last section, the U.S. Chronic Disease Indicators workflow. Let's go through an expression and view the output displaying multiple fields that have been updated.
Expression #1: Create two new fields for LocationAbbr and LocationDesc to update the values in both fields to all uppercase.
Step 1: Select the Multi-Field Formula tool from the Preparation tool palette and drag it onto the canvas. Connect the Multi-Field Formula tool to the Multi-Row Formula tool.
Step 2: Select Text from the dropdown field options.
Step 3: Select LocationAbbr and LocationDesc from the available Text fields.
Step 4: Select Copy Output Fields Add and enter Uppercase_ as a Prefix.
Step 5: Write the following expression:
UPPERCASE([_CurrentField_])
Step 6: Add the Browse tool to the Multi-Field Formula tool.
Step 7: Run the workflow:
Notice the two new fields now contain...
The ability to create fast and accurate expressions can be accomplished and optimized by using the Formula, Multi-Row Formula, and Multi-Field Formula tools. Flexible and dynamic, they save you time when it comes to developing an efficient workflow. Alteryx makes it easy to create these formulas by providing helpful options for them. Whether it's updating an existing field, or creating a new field using a variety of functions, you have explored the power of creating calculations to meet customer needs. The next chapter you will dive into how to best structure and aggregate your data.
The tools you will learn in the next chapter are frequently used when developing your efficient workflows.
