This section contains an index to all of the functions available in Wrangle .
Aggregate Functions
Item
Description
ANY Function
Extracts a non-null and non-missing value from a specified column. If all values are missing or null, the function returns a null value.
See ANY Function.
ANYIF Function
Selects a single non-null value from rows in each group that meet a specific condition.
See ANYIF Function.
AVERAGE Function
Computes the average (mean) from all row values in a column or group. Input column can be of Integer or Decimal.
See AVERAGE Function.
AVERAGEIF Function
Generates the average value of rows in each group that meet a specific condition. Generated value is of Decimal type.
See AVERAGEIF Function.
CORREL Function
Computes the correlation coefficient between two columns. Source values can be of Integer or Decimal type.
See CORREL Function.
COUNTA Function
Generates the count of non-null rows in a specified column, optionally counted by group. Generated value is of Integer type.
See COUNTA Function.
COUNTAIF Function
Generates the count of non-null values for rows in each group that meet a specific condition.
See COUNTAIF Function.
COUNTDISTINCT Function
Generates the count of distinct values in a specified column, optionally counted by group. Generated value is of Integer type.
See COUNTDISTINCT Function.
COUNTDISTINCTIF Function
Generates the count of distinct non-null values for rows in each group that meet a specific condition.
See COUNTDISTINCTIF Function.
COUNT Function
Generates the count of rows in the dataset. Generated value is of Integer type.
See COUNT Function.
COUNTIF Function
Generates the count of rows in each group that meet a specific condition. Generated value is of Integer type.
See COUNTIF Function.
COVAR Function
Computes the covariance between two columns using the population method. Source values can be of Integer or Decimal type.
See COVAR Function.
COVARSAMP Function
Computes the covariance between two columns using the sample method. Source values can be of Integer or Decimal type.
See COVARSAMP Function.
KTHLARGEST Function
Extracts the ranked value from the values in a column, where
k=1
returns the maximum value. The value for k
must be between 1 and 1000, inclusive. Inputs can be Integer, Decimal, or Datetime.
See KTHLARGEST Function.
KTHLARGESTIF Function
Extracts the ranked value from the values in a column, where
k=1
returns the maximum value, when a specified condition is met. The value for k
must be between 1 and 1000, inclusive. Inputs can be Integer, Decimal, or Datetime.
See KTHLARGESTIF Function.
KTHLARGESTUNIQUE Function
Extracts the ranked unique value from the values in a column, where
k=1
returns the maximum value. The value for k
must be between 1 and 1000, inclusive. Inputs can be Integer, Decimal, or Datetime.
See KTHLARGESTUNIQUE Function.
KTHLARGESTUNIQUEIF Function
Extracts the ranked unique value from the values in a column, where
k=1
returns the maximum value, when a specified condition is met. The value for k
must be between 1 and 1000, inclusive. Inputs can be Integer, Decimal, or Datetime.
See KTHLARGESTUNIQUEIF Function.
LIST Function
Extracts the set of values from a column into an array stored in a new column. This function is typically part of an aggregation.
See LIST Function.
LISTIF Function
Returns list of all values in a column for rows that match a specified condition.
See LISTIF Function.
MAX Function
Computes the maximum value found in all row values in a column. Inputs can be Integer, Decimal, or Datetime.
See MAX Function.
MAXIF Function
Generates the maximum value of rows in each group that meet a specific condition. Inputs can be Integer, Decimal, or Datetime.
See MAXIF Function.
MEDIAN Function
Computes the median from all row values in a column or group. Input column can be of Integer or Decimal.
See MEDIAN Function.
MIN Function
Computes the minimum value found in all row values in a column. Input column can be of Integer, Decimal or Datetime.
See MIN Function.
MINIF Function
Generates the minimum value of rows in each group that meet a specific condition. Inputs can be Integer, Decimal, or Datetime.
See MINIF Function.
MODE Function
Computes the mode (most frequent value) from all row values in a column, according to their grouping. Input column can be of Integer, Decimal, or Datetime type.
See MODE Function.
MODEIF Function
Computes the mode (most frequent value) from all row values in a column, according to their grouping. Input column can be of Integer, Decimal, or Datetime type.
See MODEIF Function.
PERCENTILE Function
Computes a specified percentile across all row values in a column or group. Input column can be of Integer or Decimal.
See PERCENTILE Function.
QUARTILE Function
Computes a specified quartile across all row values in a column or group. Input column can be of Integer or Decimal.
See QUARTILE Function.
STDEV Function
Computes the standard deviation across all column values of Integer or Decimal type.
See STDEV Function.
STDEVIF Function
Generates the standard deviation of values by group in a column that meet a specific condition.
See STDEVIF Function.
STDEVSAMP Function
Computes the standard deviation across column values of Integer or Decimal type using the sample statistical method.
See STDEVSAMP Function.
STDEVSAMPIF Function
Generates the standard deviation of values by group in a column that meet a specific condition using the sample statistical method.
See STDEVSAMPIF Function.
SUM Function
Computes the sum of all values found in all row values in a column. Input column can be of Integer or Decimal.
See SUM Function.
SUMIF Function
Generates the sum of rows in each group that meet a specific condition.
See SUMIF Function.
UNIQUE Function
Extracts the set of unique values from a column into an array stored in a new column. This function is typically part of an aggregation.
See UNIQUE Function.
VAR Function
Computes the variance among all values in a column. Input column can be of Integer or Decimal. If no numeric values are detected in the input column, the function returns
0
.
See VAR Function.
VARIF Function
Generates the variance of values by group in a column that meet a specific condition.
See VARIF Function.
VARSAMP Function
Computes the variance among all values in a column using the sample statistical method. Input column can be of Integer or Decimal. If no numeric values are detected in the input column, the function returns
0
.
See VARSAMP Function.
VARSAMPIF Function
Generates the variance of values by group in a column that meet a specific condition using the sample statistical method.
See VARSAMPIF Function.
Logical Functions
Item
Description
Logical Operators
Logical operators (and, or, not) enable you to logically combine multiple expressions to evaluate a larger, more complex expression whose output is
true
or false
.
See Logical Operators.
AND Function
Returns
true
if both arguments evaluate to true
. Equivalent to the &&
operator.
See AND Function.
OR Function
Returns
true
if either argument evaluates to true
. Equivalent to the ||
operator.
See OR Function.
NOT Function
Returns
true
if the argument evaluates to false
, and vice-versa. Equivalent to the !
operator.
See NOT Function.
Comparison Functions
Item
Description
Comparison Operators
Comparison operators enable you to compare values in the left-hand side of an expression to the values in the right-hand side of an expression.
See Comparison Operators.
ISEVEN Function
Returns
true
if the argument is an even value. Argument can be an Integer, a function returning Integers, or a column reference.
See ISEVEN Function.
ISODD Function
Returns
true
if the argument is an odd value. Argument can be an Integer, a function returning Integers, or a column reference.
See ISODD Function.
IN Function
Returns
true
if the first parameter is contained in the array of values in the second parameter.
See IN Function.
MATCHES Function
Returns
true
if a value contains a string or pattern. The value to search can be a string literal, a function returning a string, or a reference to a column of String type.
See MATCHES Function.
EQUAL Function
Returns
true
if the first argument is equal to the second argument. Equivalent to the =
operator.
See EQUAL Function.
NOTEQUAL Function
Returns
true
if the first argument is not equal to the second argument. Equivalent to the <>
or !=
operator.
See NOTEQUAL Function.
GREATERTHAN Function
Returns
true
if the first argument is greater than but not equal to the second argument. Equivalent to the >
operator.
See GREATERTHAN Function.
GREATERTHANEQUAL Function
Returns
true
if the first argument is greater than or equal to the second argument. Equivalent to the >=
operator.
See GREATERTHANEQUAL Function.
LESSTHAN Function
Returns
true
if the first argument is less than but not equal to the second argument. Equivalent to the <
operator.
See LESSTHAN Function.
LESSTHANEQUAL Function
Returns
true
if the first argument is less than or equal to the second argument. Equivalent to the <=
operator.
See LESSTHANEQUAL Function.
Math Functions
Item
Description
Numeric Operators
Numeric operators enable you to generate new values based on a computation (e.g.
3 + 4
).
See Numeric Operators.
NUMFORMAT Function
Formats a numeric set of values according to the specified number formatting. Source values can be a literal value, a function returning a numeric value, or reference to a column containing Integer or Decimal values.
See NUMFORMAT Function.
ADD Function
Returns the value of summing the first argument and the second argument. Equivalent to the
+
operator.
See ADD Function.
SUBTRACT Function
Returns the value of subtracting the second argument from the first argument. Equivalent to the
-
operator.
See SUBTRACT Function.
MULTIPLY Function
Returns the value of multiplying the first argument by the second argument. Equivalent to the
*
operator.
See MULTIPLY Function.
DIVIDE Function
Returns the value of dividing the first argument by the second argument. Equivalent to the
/
operator.
See DIVIDE Function.
MOD Function
Returns the modulo value, which is the remainder of dividing the first argument by the second argument. Equivalent to the
%
operator.
See MOD Function.
NEGATE Function
Returns the opposite of the value that is the first argument. Equivalent to the
-
operator placed in front of the argument.
See NEGATE Function.
SIGN Function
Computes the positive or negative sign of a given numeric value. The value can be a Decimal or Integer literal, a function returning Decimal or Integer, or a reference to a column containing numeric values.
See SIGN Function.
LCM Function
Returns the least common multiple shared by the first and second arguments.
See LCM Function.
ABS Function
Computes the absolute value of a given numeric value. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See ABS Function.
EXP Function
Computes the value of e raised to the specified power. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See EXP Function.
LOG Function
Computes the logarithm of the first argument with a base of the second argument.
See LOG Function.
LN Function
Computes the natural logarithm of an input value. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See LN Function.
POW Function
Computes the value of the first argument raised to the value of the second argument.
See POW Function.
SQRT Function
Computes the square root of the input parameter. Input value can be a Decimal or Integer literal or a reference to a column containing numeric values. All generated values are non-negative.
See SQRT Function.
CEILING Function
Computes the ceiling of a value, which is the smallest integer that is greater than the input value. Input can be an Integer, a Decimal, a column reference, or an expression.
See CEILING Function.
FLOOR Function
Computes the largest integer that is not more than the input value. Input can be an Integer, a Decimal, a column reference, or an expression.
See FLOOR Function.
ROUND Function
Rounds input value to the nearest integer. Input can be an Integer, a Decimal, a column reference, or an expression. Optional second argument can be used to specify the number of digits to which to round.
See ROUND Function.
TRUNC Function
Removes all digits to the right of the decimal point for any value. Optionally, you can specify the number of digits to which to round. Input can be an Integer, a Decimal, a column reference, or an expression.
See TRUNC Function.
Trigonometry Functions
Item
Description
SIN Function
Computes the sine of an input value for an angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See SIN Function.
COS Function
Computes the cosine of an input value for an angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See COS Function.
TAN Function
Computes the tangent of an input value for an angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See TAN Function.
ASIN Function
For input values between -1 and 1 inclusive, this function returns the angle in radians whose sine value is the input. This function is the inverse of the sine function. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See ASIN Function.
ACOS Function
For input values between -1 and 1 inclusive, this function returns the angle in radians whose cosine value is the input. This function is the inverse of the cosine function. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See ACOS Function.
ATAN Function
For input values between -1 and 1 inclusive, this function returns the angle in radians whose tangent value is the input. This function is the inverse of the tangent function. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See ATAN Function.
SINH Function
Computes the hyperbolic sine of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See SINH Function.
COSH Function
Computes the hyperbolic cosine of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See COSH Function.
TANH Function
Computes the hyperbolic tangent of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See TANH Function.
ASINH Function
Computes the arcsine of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See ASINH Function.
ACOSH Function
Computes the arccosine of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See ACOSH Function.
ATANH Function
Computes the arctangent of an input value for a hyperbolic angle measured in radians. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See ATANH Function.
DEGREES Function
Computes the degrees of an input value measuring the radians of an angle. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See DEGREES Function.
RADIANS Function
Computes the radians of an input value measuring degrees of an angle. The value can be a Decimal or Integer literal or a reference to a column containing numeric values.
See RADIANS Function.
Date Functions
Item
Description
DATE Function
Generates a date value from three inputs of Integer type: year, month, and day.
See DATE Function.
TIME Function
Generates time values from three inputs of Integer type: hour, minute, and second.
See TIME Function.
DATETIME Function
Generates a Datetime value from the following inputs of Integer type: year, month, day, hour, minute, and second.
See DATETIME Function.
DATEADD Function
Add a specified number of units to a valid date. Units can be any supported Datetime unit (e.g.
minute
, month
, year
, etc.). Input must be a column reference containing dates.
See DATEADD Function.
DATEDIF Function
Calculates the difference between two valid date values for the specified units of measure.
See DATEDIF Function.
DATEFORMAT Function
Formats a specified Datetime set of values according to the specified date format. Source values can be a reference to a column containing Datetime values.
See DATEFORMAT Function.
UNIXTIMEFORMAT Function
Formats a set of Unix timestamps according to a specified date formatting string.
See UNIXTIMEFORMAT Function.
MONTH Function
Derives the month integer value from a Datetime value. Source value can be a a reference to a column containing Datetime values or a literal.
See MONTH Function.
MONTHNAME Function
Derives the full name from a Datetime value as a String. Source value can be a reference to a column containing Datetime values or a literal.
See MONTHNAME Function.
YEAR Function
Derives the four-digit year value from a Datetime value. Source value can be a a reference to a column containing Datetime values or a literal.
See YEAR Function.
DAY Function
Derives the numeric day value from a Datetime value. Source value can be a a reference to a column containing Datetime values or a literal.
See DAY Function.
WEEKNUM Function
Derives the numeric value for the week within the year (
1
, 2
, etc.). Input must be the output of the DATE
function or a reference to a column containing Datetime values. The output of this function increments on Sunday.
See WEEKNUM Function.
WEEKDAY Function
Derives the numeric value for the day of the week (
1
, 2
, etc.). Input must be a reference to a column containing Datetime values.
See WEEKDAY Function.
HOUR Function
Derives the hour value from a Datetime value. Generated hours are expressed according to the 24-hour clock.
See HOUR Function.
MINUTE Function
Derives the minutes value from a Datetime value. Minutes are expressed as integers from 0 to 59.
See MINUTE Function.
SECOND Function
Derives the seconds value from a Datetime value. Source value can be a a reference to a column containing Datetime values or a literal.
See SECOND Function.
UNIXTIME Function
Derives the Unixtime (or epoch time) value from a Datetime value. Source value can be a reference to a column containing Datetime values.
See UNIXTIME Function.
NOW Function
Derives the timestamp for the current time in UTC time zone. You can specify a different time zone by optional parameter.
See NOW Function.
TODAY Function
Derives the value for the current date in UTC time zone. You can specify a different time zone by optional parameter.
See TODAY Function.
PARSEDATE Function
Evaluates an input against the default input formats or (if specified) an array of Datetime format strings in their listed order. If the input matches one of the formats, the function outputs a Datetime value.
See PARSEDATE Function.
NETWORKDAYS Function
Calculates the number of working days between two specified dates, assuming Monday - Friday workweek. Optional list of holidays can be specified.
See NETWORKDAYS Function.
NETWORKDAYSINTL Function
Calculates the number of working days between two specified dates. Optionally, you can specify which days of the week are working days as an input parameter. Optional list of holidays can be specified.
See NETWORKDAYSINTL Function.
MINDATE Function
Computes the minimum value found in all row values in a Datetime column.
See MINDATE Function.
MAXDATE Function
Computes the maximum value found in all row values in a Datetime column.
See MAXDATE Function.
MODEDATE Function
Computes the most frequent (mode) value found in all row values in a Datetime column.
See MODEDATE Function.
WORKDAY Function
Calculates the work date that is before or after a start date, as specified by a number of days. A set of holiday dates can be optionally specified.
See WORKDAY Function.
WORKDAYINTL Function
Calculates the work date that is before or after a start date, as specified by a number of days. You can also specify which days of the week are working days and a list of holidays via parameters.
See WORKDAYINTL Function.
CONVERTFROMUTC Function
Converts Datetime value to corresponding value of the specified time zone. Input can be a column of Datetime values, a literal Datetime value, or a function returning Datetime values.
See CONVERTFROMUTC Function.
CONVERTTOUTC Function
Converts Datetime value in specified time zone to corresponding value in UTC time zone. Input can be a column of Datetime values, a literal Datetime value, or a function returning Datetime values.
See CONVERTTOUTC Function.
CONVERTTIMEZONE Function
Converts Datetime value in specified time zone to corresponding value second specified time zone. Input can be a column of Datetime values, a literal Datetime value, or a function returning Datetime values.
See CONVERTTIMEZONE Function.
String Functions
Item
Description
CHAR Function
Generates the Unicode character corresponding to an inputted Integer value.
See CHAR Function.
UNICODE Function
Generates the Unicode index value for the first character of the input string.
See UNICODE Function.
UPPER Function
All alphabetical characters in the input value are converted to uppercase in the output value.
See UPPER Function.
LOWER Function
All alphabetical characters in the input value are converted to lowercase in the output value.
See LOWER Function.
PROPER Function
Converts an input string to propercase. Input can be a column reference or a string literal.
See PROPER Function.
TRIM Function
Removes leading and trailing whitespace from a string. Spacing between words is not removed.
See TRIM Function.
REMOVEWHITESPACE Function
Removes all whitespace from a string, including leading and trailing whitespace and all whitespace within the string.
See REMOVEWHITESPACE Function.
REMOVESYMBOLS Function
Removes all characters from a string that are not letters, numbers, accented Latin characters, or whitespace.
See REMOVESYMBOLS Function.
LEN Function
Returns the number of characters in a specified string. String value can be a column reference or string literal.
See LEN Function.
FIND Function
Returns the index value in the input string where a specified matching string is located in provided column, string literal, or function returning a string. Search is conducted left-to-right.
See FIND Function.
RIGHTFIND Function
Returns the index value in the input string where the last instance of a matching string is located. Search is conducted right-to-left.
See RIGHTFIND Function.
SUBSTRING Function
Matches some or all of a string, based on the user-defined starting and ending index values within the string.
See SUBSTRING Function.
SUBSTITUTE Function
Replaces found string literal or pattern or column with a string, column, or function returning strings.
See SUBSTITUTE Function.
LEFT Function
Matches the leftmost set of characters in a string, as specified by parameter. The string can be specified as a column reference or a string literal.
See LEFT Function.
RIGHT Function
Matches the right set of characters in a string, as specified by parameter. The string can be specified as a column reference or a string literal.
See RIGHT Function.
PAD Function
Pads string values to be a specified minimum length by adding a designated character to the left or right end of the string. Returned value is of String type.
See PAD Function.
MERGE Function
Merges two or more columns of String type to generate output of String type. Optionally, you can insert a delimiter between the merged values.
See MERGE Function.
STARTSWITH Function
Returns
true
if the leftmost set of characters of a column of values matches a pattern. The source value can be any data type, and the pattern can be a Trifacta pattern, regular expression, or a string.
See STARTSWITH Function.
ENDSWITH Function
Returns
true
if the rightmost set of characters of a column of values matches a pattern. The source value can be any data type, and the pattern can be a Trifacta pattern, regular expression, or a string.
See ENDSWITH Function.
REPEAT Function
Repeats a string a specified number of times. The string can be specified as a String literal, a function returning a String, or a column reference.
See REPEAT Function.
EXACT Function
Returns
true
if the second string evaluates to be an exact match of the first string. Source values can be string literals, column references, or expressions that evaluate to strings.
See EXACT Function.
STRINGGREATERTHAN Function
Returns
true
if the first string evaluates to be greater than the second string, based on a set of common collation rules.
See STRINGGREATERTHAN Function.
STRINGGREATERTHANEQUAL Function
Returns
true
if the first string evaluates to be greater than or equal to the second string, based on a set of common collation rules.
See STRINGGREATERTHANEQUAL Function.
STRINGLESSTHAN Function
Returns
true
if the first string evaluates to be less than the second string, based on a set of common collation rules.
See STRINGLESSTHAN Function.
STRINGLESSTHANEQUAL Function
Returns
true
if the first string evaluates to be less than or equal to the second string, based on a set of common collation rules.
See STRINGLESSTHANEQUAL Function.
DOUBLEMETAPHONE Function
Returns a two-element array of primary and secondary phonetic encodings for an input string, based on the Double Metaphone algorithm.
See DOUBLEMETAPHONE Function.
DOUBLEMETAPHONEEQUALS Function
Compares two input strings using the Double Metaphone algorithm. An optional threshold parameter can be modified to adjust the tolerance for matching.
See DOUBLEMETAPHONEEQUALS Function.
TRANSLITERATE Function
Transliterates Asian script characters from one script form to another. The string can be specified as a column reference or a string literal.
See TRANSLITERATE Function.
Nested Functions
Item
Description
ARRAYCONCAT Function
Combines the elements of one array with another, listing all elements of the first array before listing all elements of the second array.
See ARRAYCONCAT Function.
ARRAYCROSS Function
Generates a nested array containing the cross-product of all elements in two or more arrays.
See ARRAYCROSS Function.
ARRAYELEMENTAT Function
Computes the 0-based index value for an array element in the specified column, array literal, or function that returns an array.
See ARRAYELEMENTAT Function.
ARRAYINDEXOF Function
Computes the index at which a specified element is first found within an array. Indexing is left to right.
See ARRAYINDEXOF Function.
ARRAYINTERSECT Function
Generates an array containing all elements that appear in multiple input arrays, referenced as column names or array literals.
See ARRAYINTERSECT Function.
ARRAYLEN Function
Computes the number of elements in the arrays in the specified column, array literal, or function that returns an array.
See ARRAYLEN Function.
ARRAYMERGEELEMENTS Function
Merges the elements of an array in left to right order into a string. Values are optionally delimited by a provided delimiter.
See ARRAYMERGEELEMENTS Function.
ARRAYRIGHTINDEXOF Function
Computes the index at which a specified element is first found within an array, when searching right to left. Returned value is based on left-to-right indexing.
See ARRAYRIGHTINDEXOF Function.
ARRAYSLICE Function
Returns an array containing a slice of the input array, as determined by starting and ending index parameters.
See ARRAYSLICE Function.
ARRAYSORT Function
Sorts array values in the specified column, array literal, or function that returns an array in ascending or descending order.
See ARRAYSORT Function.
ARRAYSTOMAP Function
Combines one array containing keys and another array containing values into an Object of key-value pairs.
See ARRAYSTOMAP Function.
ARRAYUNIQUE Function
Generates an array of all unique elements among one or more arrays.
See ARRAYUNIQUE Function.
ARRAYZIP Function
Combines multiple arrays into a single nested array, with element 1 of array 1 paired with element 2 of array 2 and so on. Arrays are expressed as column names or as array literals.
See ARRAYZIP Function.
FILTEROBJECT Function
Filters the keys and values from an Object data type column based on a specified key value.
See FILTEROBJECT Function.
KEYS Function
Extracts the key values from an Object data type column and stores them in an array of String values.
See KEYS Function.
LISTAVERAGE Function
Computes the average of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
See LISTAVERAGE Function.
LISTMAX Function
Computes the maximum of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
See LISTMAX Function.
LISTMIN Function
Computes the minimum of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
See LISTMIN Function.
LISTMODE Function
Computes the most common value of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
See LISTMODE Function.
LISTSTDEV Function
Computes the standard deviation of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
See LISTSTDEV Function.
LISTSUM Function
Computes the sum of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
See LISTSUM Function.
LISTVAR Function
Computes the variance of all numeric values found in input array. Input can be an array literal, a column of arrays, or a function returning an array. Input values must be of Integer or Decimal type.
See LISTVAR Function.
Type Functions
Item
Description
NULL Function
The
NULL
function generates null values.
See NULL Function.
IFNULL Function
The
IFNULL
function writes out a specified value if the source value is a null. Otherwise, it writes the source value. Input can be a literal, a column reference, or a function.
See IFNULL Function.
IFMISSING Function
The
IFMISSING
function writes out a specified value if the source value is a null or missing value. Otherwise, it writes the source value. Input can be a literal, a column reference, or a function.
See IFMISSING Function.
IFMISMATCHED Function
The
IFMISMATCHED
function writes out a specified value if the input expression does not match the specified data type or typing array. Otherwise, it writes the source value. Input can be a literal, a column reference, or a function.
See IFMISMATCHED Function.
IFVALID Function
The
IFVALID
function writes out a specified value if the input expression matches the specified data type. Otherwise, it writes the source value. Input can be a literal, a column reference, or a function.
See IFVALID Function.
ISNULL Function
The
ISNULL
function tests whether a column of values contains null values. For input column references, this function returns true
or false
.
See ISNULL Function.
ISMISSING Function
The
ISMISSING
function tests whether a column of values is missing or null. For input column references, this function returns true
or false
.
See ISMISSING Function.
ISMISMATCHED Function
Tests whether a set of values is not valid for a specified data type.
See ISMISMATCHED Function.
VALID Function
Tests whether a set of values is valid for a specified data type and is not a null value.
See VALID Function.
PARSEINT Function
Evaluates a String input against the Integer datatype. If the input matches, the function outputs an Integer value. Input can be a literal, a column of values, or a function returning String values.
See PARSEINT Function.
PARSEBOOL Function
Evaluates a String input against the Boolean datatype. If the input matches, the function outputs a Boolean value. Input can be a literal, a column of values, or a function returning String values.
See PARSEBOOL Function.
PARSEFLOAT Function
Evaluates a String input against the Decimal datatype. If the input matches, the function outputs a Decimal value. Input can be a literal, a column of values, or a function returning String values.
See PARSEFLOAT Function.
Window Functions
Item
Description
PREV Function
Extracts the value from a column that is a specified number of rows before the current value.
See PREV Function.
NEXT Function
Extracts the value from a column that is a specified number of rows after the current value.
See NEXT Function.
FILL Function
Fills any missing or null values in the specified column with the most recent non-blank value, as determined by the specified window of rows before and after the blank value.
See FILL Function.
RANK Function
Computes the rank of an ordered set of value within groups. Tie values are assigned the same rank, and the next ranking is incremented by the number of tie values.
See RANK Function.
DENSERANK Function
Computes the rank of an ordered set of value within groups. Tie values are assigned the same rank, and the next ranking is incremented by 1.
See DENSERANK Function.
ROLLINGAVERAGE Function
Computes the rolling average of values forward or backward of the current row within the specified column.
See ROLLINGAVERAGE Function.
ROLLINGMODE Function
Computes the rolling mode (most common value) forward or backward of the current row within the specified column. Input values can be Integer, Decimal, or Datetime data type.
See ROLLINGMODE Function.
ROLLINGMAX Function
Computes the rolling maximum of values forward or backward of the current row within the specified column. Inputs can be Integer, Decimal, or Datetime.
See ROLLINGMAX Function.
ROLLINGMIN Function
Computes the rolling minimum of values forward or backward of the current row within the specified column. Inputs can be Integer, Decimal, or Datetime.
See ROLLINGMIN Function.
ROLLINGSUM Function
Computes the rolling sum of values forward or backward of the current row within the specified column.
See ROLLINGSUM Function.
ROLLINGSTDEV Function
Computes the rolling standard deviation of values forward or backward of the current row within the specified column.
See ROLLINGSTDEV Function.
ROLLINGSTDEVSAMP Function
Computes the rolling standard deviation of values forward or backward of the current row within the specified column using the sample statistical method.
See ROLLINGSTDEVSAMP Function.
ROLLINGVAR Function
Computes the rolling variance of values forward or backward of the current row within the specified column.
See ROLLINGVAR Function.
ROLLINGVARSAMP Function
Computes the rolling variance of values forward or backward of the current row within the specified column using the sample statistical method.
See ROLLINGVARSAMP Function.
ROLLINGCOUNTA Function
Computes the rolling count of non-null values forward or backward of the current row within the specified column.
See ROLLINGCOUNTA Function.
ROLLINGKTHLARGEST Function
Computes the rolling kth largest value forward or backward of the current row. Inputs can be Integer, Decimal, or Datetime.
See ROLLINGKTHLARGEST Function.
ROLLINGKTHLARGESTUNIQUE Function
Computes the rolling unique kth largest value forward or backward of the current row. Inputs can be Integer, Decimal, or Datetime.
See ROLLINGKTHLARGESTUNIQUE Function.
ROLLINGLIST Function
Computes the rolling list of values forward or backward of the current row within the specified column and returns an array of these values.
See ROLLINGLIST Function.
ROWNUMBER Function
Generates a new column containing the row number as sorted by the
order
parameter and optionally grouped by the group
parameter.
See ROWNUMBER Function.
SESSION Function
Generates a new session identifier based on a sorted column of timestamps and a specified rolling timeframe.
See SESSION Function.
Other Functions
Item
Description
COALESCE Function
Function returns the first non-missing value found in an array of columns.
See COALESCE Function.
RAND Function
The
RAND
function generates a random real number between 0 and 1. The function accepts an optional integer parameter, which causes the same set of random numbers to be generated with each job execution.
See RAND Function.
RANDBETWEEN Function
Generates a random integer between a low and a high number. Two inputs may be Integer or Decimal types, functions returning these types, or column references.
See RANDBETWEEN Function.
PI Function
The
PI
function generates the value of pi to 15 decimal places: 3.1415926535897932.
See PI Function.
SOURCEROWNUMBER Function
Returns the row number of the current row as it appeared in the original source dataset before any steps had been applied.
See SOURCEROWNUMBER Function.
IF Function
The
IF
function allows you to build if/then/else conditional logic within your transforms.
See IF Function.
CASE Function
The
CASE
function allows you to perform multiple conditional tests on a set of expressions within a single statement. When a test evaluates to true
, a corresponding output is generated. Outputs may be a literal or expression.
See CASE Function.
Ternary Operators
Ternary operators allow you to build if/then/else conditional logic within your transforms. Please use the
IF
function instead.
See Ternary Operators.
IPTOINT Function
Computes an integer value for a four-octet internet protocol (IP) address. Source value must be a valid IP address or a column reference to IP addresses.
See IPTOINT Function.
IPFROMINT Function
Computes a four-octet internet protocol (IP) address from a 32-bit integer input.
See IPFROMINT Function.
RANGE Function
Computes an array of integers, from a beginning integer to an end (stop) integer, stepping by a third parameter.
See RANGE Function.
HOST Function
Finds the host value from a valid URL. Input values must be of URL or String type and can be literals or column references.
See HOST Function.
DOMAIN Function
Finds the value for the domain from a valid URL. Input values must be of URL or String type.
See DOMAIN Function.
SUBDOMAIN Function
Finds the value a subdomain value from a valid URL. Input values must be of URL or String type.
See SUBDOMAIN Function.
SUFFIX Function
Finds the suffix value after the domain from a valid URL. Input values must be of URL or String type.
See SUFFIX Function.
URLPARAMS Function
Extracts the query parameters of a URL into an Object. The Object keys are the parameter's names, and its values are the parameter's values. Input values must be of URL or String type.
See URLPARAMS Function.
This page has no comments.