Workflow Specialized Functions
These functions perform a variety of specialized actions and can be used with all Supported Data Types.
Note
Not all functions are supported between Standard mode and Cloud Native mode tools. For a list of supported functions, go to the respective Standard mode and Could Native mode function lists found on the Formula tool page.
Coalesce
Coalesce(v1,v2,v3,…,vn): Returns the first non-null value.
Important
Values passed to the Coalesce function must all be the same type (all string or all numeric).
Example
Coalesce(null, "oranges", "apples", null, "grapes")returns oranges.
Coalesce(oranges, apples, grapes)returns an error.
Coalesce("1", "2", "3")returns 1.
Coalesce(null, null, null)returns Null.
Coalesce(toDateTime("apples"), toDateTime(1))returns 1899-12-31 00:00:00 because toDateTime("apples") returns null.
EscapeXMLMetacharacters
EscapeXMLMetacharacters(String): Replaces XML metacharacters with their escaped versions. There are 5 characters that are escaped.
Character | Escaped Version |
|---|---|
" | " |
' | ' |
< | < |
> | > |
& | & |
Example
EscapeXMLMetacharacters("2>1&1<2")returns 2>1&1<2
GetVal
GetVal(index, v0,...vn): Returns the value (v0, ..., vn) specified by the 0-based index.
Message
Message(messageType, message, returnValue): Use with a conditional statement to output a message to the message log and update column data with a specified value when a condition is met.
The message type and text is shown in the Results window.
Parameters
messageType: A number that corresponds to the type of message:
1: Messages
2: Warnings
3: Errors
5: Conv Errors (field conversion errors)
8: Files (input)
9: Files (output)
message: The text of the message, expressed as a string between quotes.
returnValue: A value to output to column data. This can be a numeric value (for example, 0), null, or a text string between quotes (for example, "False").
Example
In this example, the Message function is used within a conditional expression to output a field conversion error message and update column data with a value of "False" if given date-time values do not contain valid time data.
If [Hour] = "0" and [Minutes] = "0" then Message(5, "Invalid time in DateTime", "False") Else "True" Endif
Null
Null(): Returns a Null value.
RangeMedian
RangeMedian(...): Calculates the median from a series of aggregated ranges. Go to Range Median for more information.
Soundex
Soundex(String): Returns the Soundex of String. Soundex creates a code based on the first character in the string plus 3 characters based on these items:
Character | Soundex |
|---|---|
Non-Alpha Characters (numbers and punctuation) | -1 |
a, e, i, o, u, y, h, and w | Ignored unless it's the first character in the string. |
b, f, p, and v | 1 |
c, g, j, k, q, s, x, and z | 2 |
d and t | 3 |
l | 4 |
m and n | 5 |
r | 6 |
Example
If the resulting code is only 2 or 3 characters long, Soundex uses zeros to fill out the code to 4 characters. For example, in the name Lauren, only the L, r, and n are translated (Lrn), so the resulting Soundex code is L650.
If the resulting code is more than 4 characters long, all characters after the fourth character are ignored. For example, in the name Patrick, the P, t, r, c, and k can be translated (Ptrck), but the resulting Soundex code is only 4 characters: P362.
Soundex_Digits
Soundex_Digits(String): Returns the first 4 digits or the Soundex if none.
If there are digits (numbers) in the String, the first 4 digits are returned.
If there are no digits, the Soundex code is returned.
Example String | Soundex_Digits |
|---|---|
3825 Iris | 3825 |
55555 Main | 5555 |
14L Broadway | 14 |
Commerce Street | C562 |
L Street | L236 |
TOPNIDX
TOPNIDX(N, v0, v1, ..., vn): Returns the 0-based original index position of the Nth from the maximum value upon sorting the indexed fields (v0 to vn) in descending order. Null values are ignored and N should be less than non-null parameters. With N==0, it is the same as MaxIdx(...).
N- Nth from the maximum value from the indexed fields.v0, v1, ..., vnare the indexed fields.
Example
TOPNIDX(1, [IndexedField1], 5, 7)returns...
0 if 5<
[IndexedField1]<71 if
[IndexedField1]< 5 or[IndexedField1]is null2 if
[IndexedField1]> 7
UrlEncode
UrlEncode(String): Encodes a UTF-16 string using a non-standard, UTF-16-based percent-encoding.
Important
Please note that this method is no longer recommended. We recommend the new UrlEncodeUTF8 function instead.
Example
UrlEncode("C:\temp\Alteryx URL Encode")returns C:/temp/Alteryx%20URL%20Encode.
UrlEncodeUTF8
UrlEncodeUTF8(String): Encodes a string using the RFC 3986-compliant* percent-encoding. The function converts characters into UTF8-based units and it skips characters defined in the unreserved character set. Unlike the above UrlEncode function, this function doesn't skip the common URL delimiters like "/", so it's not suitable for encoding full path URLs. You should use this function to encode specific URL components, like query component parameters.
Note
*RFC 3986 is the standard for URL encoding and defines a method for encoding Unicode characters by converting them into a UTF-8 byte sequence and then representing each byte with a % (percent) sign followed by two hexadecimal digits.
Example
UrlEncodeUTF8("C:\temp\Alteryx URL Encode")returns C%3A%5Ctemp%5CAlteryx%20URL%20Encode.