This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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:
The Dynamic Replace Tool is an under-utilized tool in the Developer Toolset that is very powerful. It allows for dynamic formulas or conditions to be used in your workflow. It was first introduced in Alteryx 6.1. It’s one of the few tools that is currently multi-threaded which makes is fast.
The RegEx tool is kind of like the Swiss Army Knife of parsing in Alteryx; there are a whole lot of ways you can use it to do things faster or more effectively, but even if you just use the blade it's still immensely useful. Sometimes that's all you need, but if you do take the time to figure out how to use a few other tools in that knife, you'll start to see that there isn't much you can't do with it.
The Multi-Row Formula Tool functions much like the normal Formula Tool but adds the ability to reference multiple rows of data within one expression. Say, for example, someone was on the ground floor of a house and had a Formula Tool. They would only be able to talk to the people also on the ground floor. If they had a Multi-Row Formula Tool, though, they would also be able to talk to the people upstairs, in the attic, and in the basement as well.
There are a handful of ways to search for a particular string within a data field. If you want to perform a query, identifying records with a particular string field within a data field:
Use the Filter tool: the result will be two streams - those records that meet your filter criteria and those that do not.
On the Functions Tab, expand the string tree and select FINDSTRING(String, Target)
Replace the `String` parameter with the field name
Replace the `Target` parameter with the value you are looking to identify
Finish the expression with !=-1 which will separate the true values from the false ones.
Example - If you are trying to identify all of the customers with Joe in a data field [Name] :
The records that meet this criteria will be output from the True anchor ([Name] contains the value "Joe"). All other records will be output from the False anchor.
This function can also be used in the Formula tool; if for example you want to populate a different data column based on the [Name] field, you can use the FINDSTRING in an IF statement. Example: You would like to classify your data in a new field based on the instance of customers with Joe in a data field [Name] :
IF (FINDSTRING([Name], "Joe") != -1) THEN "JOE Customer" ELSE "Other" ENDIF
This will populate a new data field with "JOE Customer" if the field "Name" contains "Joe" otherwise it will populate that field with the value "Other"
In the Formula tool, add a new field by selecting + Add Column, or choose an existing field to update.
Make sure the appropriate Field Type and Size is also specified
On the Functions Tab, expand the Conditional Tree and select IF c THEN t ELSE f ENDIF
On the Functions Tab, expand the String tree and select FINDSTRING(String, Target) to replace c
Replace `String` with the field name
Replace `Target` with the string you are looking to identify
Finish this part of the expression with !=-1 which will separate the true values from the false ones
Replace "t" with the desired value to populate the new field if the condition is met: "JOE Customer"
Replace "f" with the desired value to populate the new field if the condition is not met: "Other"