These functions perform a variety of specialized actions and can be used with all data types.
EscapeXMLMetacharacters(String): Replaces XML metacharacters with their escaped versions. There are five characters that are escaped.
EscapeXMLMetacharacters("2>1&1<2") returns 2>1&1<2
GetVal(index, v0,...vn): Returns the value (v0, ..., vn) specified by the 0-based [index].
GetEnvironmentVariable(Name): Returns the environment variable specified in Name. To get a list of environment variables, go to Control Panel > System > Advanced System Settings > Environment Variables. A list of system variables appears. Any value from this list can be used.
Alteryx doesn't support the
GetEnvironmentVariable in apps saved to the Gallery.
GetEnvironmentVariable(CommonProgramFiles) returns C:\Program Files\Common Files
GetEnvironmentVariable(OS) returns Windows_NT
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.
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").
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")
NULL(): Returns a Null value.
RangeMedian(...): Calculates the median from a series of aggregated ranges. See Range Median.
ReadRegistryString(Key, ValueName, DefaultValue="): Returns a value from the registry.
Soundex(String): Returns the Soundex of the String. Soundex creates a code based on the first character in the string plus three characters based on these items:
|Non-Alpha Characters (numbers and punctuation)||-1|
|a, e, i, o, u, y, h, and w||Ignored unless 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|
|m and n||5|
If the resulting code is only 2 or 3 characters long, Soundex uses zeros to fill out the code to four characters. For example, in the name Lauren, only the L, r, and n are translated (Lrn), so the resulting Soundex code becomes L650.If the resulting code is more than four 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 will only be four characters: P362.
Soundex_Digits(String): Returns the first four digits or the Soundex if none.
- If there are digits (numbers) in the String, the first four digits are returned.
- If there are no digits, the Soundex code is returned.
TOPNIDX(N, v0, v1, ..., vn): Returns the 0-based index of the Nth from the maximum value from the list. With N==0, it is the same as MaxIdx(...).
N is the starting point,
v0 is the indexed field (subsequent Variables are optional), and vn is the goal.
TOPNIDX(0, [IndexedField], 5) returns the top five of a stream
UrlEncode(String): Encodes string as a web-legal URL.
C:\temp\Alteryx URL Encode returns C:/temp/Alteryx%20URL%20Encode