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.
A large component of data blending is applying mathematical or transformational processes to subsets of your data. Often, this requires isolating the data that complies with a certain criteria that you’ve set. The Conditional functions build expressions that allow you to apply processes to data that satisfy conditions you set.
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"
Sometimes, a dataset will contain numbers stored as text. I order to do calculations using those numbers, the datatype will need to be converrted to a numeric data type. If the data is clean, changing the data type in a select tool can do the trick. Another option is to use the TONUMBER() function in a formula tool or multi field formula tool (if you have more than one field to convert).
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.
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.
A must-have for any app or macro, the Error Message Tool displays a prompt to the user based on input from Interface Tools . Using any expression that evaluates to true, along with any number of user inputs from question anchor connected Interface Tools, the Error Message Tool can layer even the most involved applications with failsafes that ease a user’s experience through more robust interactions.
Users often ask, "How do I do (x) function in Alteryx?" - here's a handy guide for translating Tableau functions for use in Alteryx!
*Please note: This is not a comprehensive list of all functions available in Alteryx and Tableau - only functions that are written differently, but perform similar tasks, are included here. For a list of all the functions available in Alteryx, please refer to our Help Documentation.
Return smallest integer greater than or equal to [x]. Works like the 'RoundUp' function in Excel.
x % y
Modulo of n divided by d - The Modulo operation finds the remainder of division of one number by another number.
Return [x] raised to the [e] power.
Return [x] rounded to nearest multiple of [mult].
IF isnull([field]) THEN 0 else [field] ENDIF
Returns the expression if it is not null, otherwise returns zero. Use this function to use zero values instead of null values.
IF Contains([field], "string") then 1 ELSE 0 ENDIF
Returns true if the given string contains the specified substring.
FIND(string, substring, [start])
Searches for the occurrence of a particular string within a data field and returns the numeric position of its occurrence in the string. In Tableau, returns the index position of substring in string or 0 if the substring isn't found. If the optional argument start is added, the function ignores any instances of substring that appear before the index position [start].*
Return the length of the string [x].
Converts a string to lower case.
REGEX_Match(string, pattern, icase)
Searches a string for an occurrence of a regular expression.
REGEX_Replace(string, pattern, replace, icase)
REGEXP_REPLACE(string, pattern, replacement)
Allows replacement of text using regular expressions and returns the string resulting from the RegEx find pattern and replace string.
Substring(x, start, length)
MID(string, start, [length])
Return the substring of [x] starting at [start] and stopping after [length], if provided.*
Remove character in the string y from the end of the string x; y is optional and defaults to trimming white space. In Tableau, this function trims extra whitespace.
Remove character in the string y from the end of the string x; y is optional and defaults to trimming white space. In Tableau, this function trims extra whitespace.
Converts a string to upper case.
Date Time Functions
DateTimeAdd(datetime, interval, units)
DATEADD(date_part, interval, date)
Return the given date/time modified by the given duration. The <interval> specifies a positive or negative integer of time to add or subtract and <units> is one of a date/time unit - "years", "months", "days", "hours", "minutes", or "seconds". For Tableau, additional date_part units are allowed.
DateTimeDiff(datetime1, datetime2, units)
DATEDIFF(date_part, date1, date2, [start_of_week])
Subtract the second argument from the first and return it as an integer difference. The duration is returned as a number, not a string, in the specified units - "years", "months", "days", "hours", "minutes", or "seconds". For Tableau, additional date_part units are allowed.
DateTimeTrim(datetime, trim type)
DATETRUNC(date_part, date, [start_of_week])
Remove unwanted portions of a date/time and return the modified date/time. Options include: firstofmonth, lastofmonth, year, month, day, hour, minute. In Tableau, truncates the specified date to the accuracy specified by the date_part. This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month. The start_of_week parameter is optional.
Returns the current system date and time.
DateTimeParse(datetime, format of incoming string)
MAKEDATE(year, month, day)
Converts a date string with a specific format to the standard ISO format yyyy-mm-dd HH:MM: SS. In Tableau, returns a date value constructed from the specified year, month, and date.
Returns today’s date. The time is set to midnight of the beginning of the day. Returns the current date.
ToNumber(x, bIgnoreErrors, keepNulls)
INT(expression) or FLOAT(expression)
Converts a string parameter to a number. The second parameter is optional and allows for ignoring conversion error messages. This parameter is a boolean flag and will accept a value of 1, 0, true or false. There is an optional 3rd parameter to handle Nulls. In Tableau, INT casts its argument as an integer. For expressions, this function truncates results to the closest integer toward zero. FLOAT casts its argument as a number with decimal/float precision.
Casts its argument as a string.
*In Alteryx, string positions start at 0. In Tableau, string positions start at 1.
Binary (bit level or bitwise) operations operate on one or more bit patterns or binary numerals at the level of their discrete bits. They are typically used to manipulate values for comparisons and calculations - and encryption!
These functions are in Formula Tool, in the Integer submenu of the Math menu:
Binary values are stored as strings, retaining the leading zeros. To use these functions, you'll have to convert these to numerals. Use the BinToInt() function to convert your binary strings to numeric values. Once you've completed your calculations, use IntToBin() to get the binary values. Note: you'll need to add your leading zeros back in, using the PadLeft function.
If you need the character associated with the binary value, use CharToInt(). Hex calculations work similarly.
BinaryAnd(n,m) – performs the logical AND operation on two binary numerals
BinaryNot(n) – performs logical negation, forming the complement (bits that are 0 become 1, and those that are 1 become 0)
BinaryXOr(n,m) - exclusive disjunction essentially means 'either one, but not both nor none'. In other words, the statement is true if and only if one is true and the other is false.
A common use for XOR is a means of doing a parity check. A bitstring has even parity if the number of 1s in the string is even. It has an odd parity if the number of 1s is odd. If you XOR the bits together, you can tell whether a bitstring has even or odd parity.
ShiftLeft(n,b) / ShiftRight(n,b) - shifting left is equivalent to multiplication by powers of 2. So 5 << 1 is equivalent to 5 * 2, and 5 << 3 is equivalent to 5 * 8. Shifting to the right is equivalent to division by powers of 2.
Please see the attached v10.5 workflow for a simple secret message conversion.
The Excel number for a modern date is most easily calculated as the number of days since 12/30/1899 on the Gregorian calendar.
Attached please find a v10.0 workflow that converts a double (Excel date) to a Datetime in Alteryx.
It uses this function in a Formula Tool:
where Datebase = a datetime value set to "1899-12-30 00:00:00"
and Field1 is double value = 42397
If you're using the Formula Tool in v11.0+, be sure to familiarize yourself with the tool's interface redesign!
In 1925, the small town of Nome in Alaska suffered a diphtheria outbreak. Out of usable antitoxin and in the depths of winter, the town doctor telegrammed the governor in Juneau and authorities in Washington D.C. requesting a million units of antitoxin to stave off an epidemic. Confronted with the task of saving the people of Nome, Alaskan authorities debated their options of getting the antitoxin to Nome. With sub-zero temperatures, limited daylight and a lack of experienced pilots, airlifting the supplies was a less than ideal solution. Instead, a dogsled relay was selected to transport the antitoxin across the 674 miles from Nenana to Nome. The mushers endured near-blizzard conditions and hurricane-force winds to deliver the serum five and a half days later, saving the small town from a deadly outbreak.
While you may not have the fate of a small town on your hands, understanding the time elements in your data is critical for your analyses. Perhaps you need to determine a product’s time in transit between two ports or warehouses, identify non-business days in your dataset to correctly account for time, or re-format data to work on a 12 or 24 hour clock. In all of these cases, time is of the essence!
To demonstrate the use of the Formula Tool’s DateTime functions, and in the spirit of the Alaskan dogsledding tradition, we’ll work with data from the last leg of the Iditarod, which stretches from Safety to Nome. Using the data provided from the 2016 race, we will 1) calculate the elapsed time for each musher between the two checkpoints, 2) determine the Iditarod’s start date and time from a musher’s total race time and arrival time in Nome, and 3) re-format the time format from a 24 to 12 hour clock. Before beginning with Formula Tool’s DateTime functions, make sure that your data should be appropriately formatted as a DateTime field type using the DateTime tool or a Select Tool.*
*The only acceptable string format for use with DateTime functions is yyyy-mm-dd HH:MM:SS, which is Alteryx’s native string format.
The Input data contains the time each musher left Safety (Departure) and the time that they arrived in Nome (Arrival), finishing the race. To calculate the time it took for each musher to complete this final leg of the race, we’ll find the difference between the start (Departure) and end (Arrival) times using the DateTimeDiff function:
DateTimeDiff (<date/time1>, <date/time2>, <units>)
[Arrival] [Departure] ‘seconds’
The expression returns the elapsed time between the Arrival and Departure times as years, months, days, hours, minutes or seconds as a numeric field type. In this case, the expression returns the number of seconds (Int64) between the Departure and Arrival times, which can be used downstream to calculate hours (Seconds/3600) and minutes (Seconds/60).
The sample dataset not only provides data for the final leg of the 2016 Iditarod but also each musher’s time for the entirety of the race (Total Race Time). Using these pieces of information, we can determine the time at which the mushers started the race in Anchorage by subtracting the musher’s total race time from their completion time (Arrival) in Nome. After a bit of parsing and simple math, we can convert a Total Race Time of 8d 11h 20m 16s to its value of 732016 seconds (Total Seconds). Then, we can subtract the race time (in seconds) from each musher’s arrival time in Nome to determine the start time of the race using the DateTimeAdd function:
DateTimeAdd (<date/time>, <interval>, <units>)
[Arrival], -[Total Seconds]*, ‘seconds’
*Because we are looking to determine a DateTime in the past, we use a negative value for the time interval. When looking for a DateTime in the future, ensure that your time interval is a positive numeric value.
After applying our DateTimeAdd function, we find that the 2016 Iditarod began at 2016-03-06 15:00:00. That’s not the easiest format to understand, so let’s take advantage of the power of the DateTime Format function. The DateTime Format tool allows you to indicate a format specifier to parse components of a DateTime field. A format specifier begins with a percent sign (%), followed by a case-sensitive letter. For a complete list of supported format specifiers, please refer to the Formula Tool’s help documentation.
For our particular example, let’s identify the day of the week, the month, date and time (on a 12 hour clock) that the race started. We use the format identifiers to create a string field that contains this information. It is important to note that each format specifier is treated as text and needs to be wrapped in single quotes (see example below).
Using a series of format specifiers, we can see that the 2016 Iditarod began on Sunday, March 6, 2016 at 3:00 PM. When time, whether when dogsledding or managing your business operations, is of the essence, you can count on the DateTime functions to save the day!
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:
You may have seen the modulo (mod) function used in the awesome Collatz Conjecture macro on the Gallery; here, it's used to determine the parity of the entered integer. Depending on if the number is even or odd, a different set of operations is applied to the value.
When writing expressions that evaluate NULL conditions, it is a common mistake to write the syntax as:
IF [FieldX] = NULL() THEN ...
After all, this is how you would write the expression in SQL. However, the actual syntax needs to be:
IF ISNULL([FieldX]) THEN ....
Keep writing those expressions!
Question If I have a list of dates, how can I find the date of the next Monday for each date?
Answer Using a simple workflow, you can calculate the next Monday from any date by using a single Formula tool and configuring as follows:
Determine the day of each date Day: DateTimeFormat([Sample Dates],"%a")
Calculate the number of days to get to the next Monday based on each day AddDays: Switch([Day],Null(),'Mon',7,'Tue',6,'Wed',5,'Thu',4,'Fri',3,'Sat',2,'Sun',1)
Add the number of days (from step 2) to get to the next Monday to each date Monday: DateTimeAdd([Sample Dates],[AddDays],"days"
Verify that new date is Monday VerifyNewDay: DateTimeFormat([Monday],"%a")
You can actually do all of this within one formula, save for verifying the day, if you want to get fancy:
Monday: DateTimeAdd([Sample Dates], Switch(DateTimeFormat([Sample Dates],"%a"),Null(),'Mon',7,'Tue',6,'Wed',5,'Thu',4,'Fri',3,'Sat',2,'Sun',1),"days")
Things to consider:
This workflow assumes that your dates are already in the Alteryx native date format of "YYYY-MM-DD". If they aren't, please visit another Knowledge Base article, Date Conversions, for tips on how to get your dates there!
This was done in 10.1. Sample workflow attached.
Thanks for tuning in!