Language Index
This section contains an index to functions available in Wrangle.
Aggregate Functions
Item | Description |
---|---|
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. | |
Selects a single non-null value from rows in each group that meet a specific condition. | |
Computes the approximate median from all row values in a column or group. Input column can be of Integer or Decimal. | |
Computes an approximation for a specified percentile across all row values in a column or group. Input column can be of Integer or Decimal. | |
Computes an approximation for a specified quartile across all row values in a column or group. Input column can be of Integer or Decimal. | |
Computes the average (mean) from all row values in a column or group. Input column can be of Integer or Decimal. | |
Generates the average value of rows in each group that meet a specific condition. Generated value is of Decimal type. | |
Computes the correlation coefficient between two columns. Source values can be of Integer or Decimal type. | |
Generates the count of non-null rows in a specified column, optionally counted by group. Generated value is of Integer type. | |
Generates the count of non-null values for rows in each group that meet a specific condition. | |
Generates the count of distinct values in a specified column, optionally counted by group. Generated value is of Integer type. | |
Generates the count of distinct non-null values for rows in each group that meet a specific condition. | |
Generates the count of rows in the dataset. Generated value is of Integer type. | |
Generates the count of rows in each group that meet a specific condition. Generated value is of Integer type. | |
Computes the covariance between two columns using the population method. Source values can be of Integer or Decimal type. | |
Computes the covariance between two columns using the sample method. Source values can be of Integer or Decimal type. | |
Extracts the ranked value from the values in a column, where | |
Extracts the ranked value from the values in a column, where | |
Extracts the ranked unique value from the values in a column, where | |
Extracts the ranked unique value from the values in a column, where | |
Extracts the set of values from a column into an array stored in a new column. This function is typically part of an aggregation. | |
Returns list of all values in a column for rows that match a specified condition. | |
Computes the maximum value found in all row values in a column. Inputs can be Integer, Decimal, or Datetime. | |
Generates the maximum value of rows in each group that meet a specific condition. Inputs can be Integer, Decimal, or Datetime. | |
Computes the median from all row values in a column or group. Input column can be of Integer or Decimal. | |
Computes the minimum value found in all row values in a column. Input column can be of Integer, Decimal or Datetime. | |
Generates the minimum value of rows in each group that meet a specific condition. Inputs can be Integer, Decimal, or Datetime. | |
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. | |
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. | |
Computes a specified percentile across all row values in a column or group. Input column can be of Integer or Decimal. | |
Computes a specified quartile across all row values in a column or group. Input column can be of Integer or Decimal. | |
Computes the standard deviation across all column values of Integer or Decimal type. | |
Generates the standard deviation of values by group in a column that meet a specific condition. | |
Computes the standard deviation across column values of Integer or Decimal type using the sample statistical method. | |
Generates the standard deviation of values by group in a column that meet a specific condition using the sample statistical method. | |
Computes the sum of all values found in all row values in a column. Input column can be of Integer or Decimal. | |
Generates the sum of rows in each group that meet a specific condition. | |
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. | |
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 | |
Generates the variance of values by group in a column that meet a specific condition. | |
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 | |
Generates the variance of values by group in a column that meet a specific condition using the sample statistical method. |
Logical Functions
Item | Description |
---|---|
Logical operators (and, or, not) enable you to logically combine multiple expressions to evaluate a larger, more complex expression whose output is | |
Returns | |
Returns | |
Returns |
Comparison Functions
Item | Description |
---|---|
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. | |
Returns | |
Returns | |
Returns | |
Returns | |
Returns | |
Returns | |
Returns | |
Returns | |
Returns | |
Returns |
Math Functions
Item | Description |
---|---|
Numeric operators enable you to generate new values based on a computation (e.g. | |
Formats a numeric set of values according to the specified number formatting. Source values can be a literal numeric value, a function returning a numeric value, or reference to a column containing an Integer or Decimal values. | |
Returns the value of summing the first argument and the second argument. Equivalent to the | |
Returns the value of subtracting the second argument from the first argument. Equivalent to the | |
Returns the value of multiplying the first argument by the second argument. Equivalent to the | |
Returns the value of dividing the first argument by the second argument. Equivalent to the | |
Returns the modulo value, which is the remainder of dividing the first argument by the second argument. Equivalent to the | |
Returns the opposite of the value that is the first argument. Equivalent to the | |
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. | |
Returns the least common multiple shared by the first and second arguments. | |
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. | |
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. | |
Computes the logarithm of the first argument with a base of the second argument. | |
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. | |
Computes the value of the first argument raised to the value of the second argument. | |
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. | |
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. | |
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. | |
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. | |
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. | |
Converts a string formatted as a number into an Integer or Decimal value by parsing out the specified decimal and group separators. A string or a function returning formatted numbers of String type or a column containing formatted numbers of string type can be inputs. |
Trigonometry Functions
Item | Description |
---|---|
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. | |
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. | |
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. | |
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. | |
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. | |
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. | |
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. | |
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. | |
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. | |
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. | |
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. | |
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. | |
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. | |
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. |
Date Functions
Item | Description |
---|---|
Generates a date value from three inputs of Integer type: year, month, and day. | |
Generates time values from three inputs of Integer type: hour, minute, and second. | |
Generates a Datetime value from the following inputs of Integer type: year, month, day, hour, minute, and second. | |
Add a specified number of units to a valid date. Units can be any supported Datetime unit (e.g. | |
Calculates the difference between two valid date values for the specified units of measure. | |
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. | |
Formats a set of Unix timestamps according to a specified date formatting string. | |
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. | |
Derives the full name from a Datetime value of the corresponding month as a String. Source value can be a reference to a column containing Datetime values or a literal. | |
Returns the serial date number for the last day of the month before or after a specified number of months from a starting date. | |
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. | |
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. | |
Derives the numeric value for the week within the year ( | |
Derives the numeric value for the day of the week ( | |
Derives the full name from a Datetime value of the corresponding weekday as a String. Source value can be a reference to a column containing Datetime values or a literal. | |
Derives the hour value from a Datetime value. Generated hours are expressed according to the 24-hour clock. | |
Derives the minutes value from a Datetime value. Minutes are expressed as integers from 0 to 59. | |
Derives the seconds value from a Datetime value. Source value can be a a reference to a column containing Datetime values or a literal. | |
Derives the Unixtime (or epoch time) value from a Datetime value. Source value can be a reference to a column containing Datetime values. | |
Derives the timestamp for the current time in UTC time zone. You can specify a different time zone by optional parameter. | |
Derives the value for the current date in UTC time zone. You can specify a different time zone by optional parameter. | |
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. | |
Calculates the number of working days between two specified dates, assuming Monday - Friday workweek. Optional list of holidays can be specified. | |
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. | |
Computes the minimum value found in all row values in a Datetime column. | |
Computes the maximum value found in all row values in a Datetime column. | |
Computes the most frequent (mode) value found in all row values in a Datetime column. | |
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. | |
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. | |
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. | |
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. | |
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. | |
Returns the minimum Datetime value of rows in each group that meet a specific condition. Set of values must valid Datetime values. | |
Returns the maximum Datetime value of rows in each group that meet a specific condition. Set of values must valid Datetime values. | |
Returns the most common Datetime value of rows in each group that meet a specific condition. Set of values must valid Datetime values. | |
Extracts the ranked Datetime value from the values in a column, where | |
Extracts the ranked unique Datetime value from the values in a column, where | |
Extracts the ranked unique Datetime value from the values in a column, where | |
Extracts the ranked Datetime value from the values in a column, where | |
Generates a serial date number from a valid date value. |
String Functions
Item | Description |
---|---|
Generates the Unicode character corresponding to an inputted Integer value. | |
Generates the Unicode index value for the first character of the input string. | |
All alphabetical characters in the input value are converted to uppercase in the output value. | |
All alphabetical characters in the input value are converted to lowercase in the output value. | |
Converts an input string to propercase. Input can be a column reference or a string literal. | |
Removes leading and trailing whitespace from a string. Spacing between words is not removed. | |
Removes all whitespace from a string, including leading and trailing whitespace and all whitespace within the string. | |
Removes all characters from a string that are not letters, numbers, accented Latin characters, or whitespace. | |
Returns the number of characters in a specified string. String value can be a column reference or string literal. | |
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. | |
Returns the index value in the input string where the last instance of a matching string is located. Search is conducted right-to-left. | |
Returns the position of the nth occurrence of a letter or pattern in the input string where a specified matching string is located in the provided column. You can search either from left or right. | |
Matches some or all of a string, based on the user-defined starting and ending index values within the string. | |
Replaces found string literal or pattern or column with a string, column, or function returning strings. | |
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. | |
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. | |
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. | |
Merges two or more columns of String type to generate output of String type. Optionally, you can insert a delimiter between the merged values. | |
Returns | |
Returns | |
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. | |
Returns | |
Returns | |
Returns | |
Returns | |
Returns | |
Returns a two-element array of primary and secondary phonetic encodings for an input string, based on the Double Metaphone algorithm. | |
Compares two input strings using the Double Metaphone algorithm. An optional threshold parameter can be modified to adjust the tolerance for matching. | |
Transliterates Asian script characters from one script form to another. The string can be specified as a column reference or a string literal. | |
Removes leading and trailing quotes or double-quotes from a string. Quote marks in the middle of the string are not removed. | |
Converts an input value to base64 encoding with optional padding with an equals sign ( | |
Converts an input base64 value to text. Output type is String. |
Nested Functions
Item | Description |
---|---|
Combines the elements of one array with another, listing all elements of the first array before listing all elements of the second array. | |
Generates a nested array containing the cross-product of all elements in two or more arrays. | |
Computes the 0-based index value for an array element in the specified column, array literal, or function that returns an array. | |
Computes the index at which a specified element is first found within an array. Indexing is left to right. | |
Generates an array containing all elements that appear in multiple input arrays, referenced as column names or array literals. | |
Computes the number of elements in the arrays in the specified column, array literal, or function that returns an array. | |
Merges the elements of an array in left to right order into a string. Values are optionally delimited by a provided delimiter. | |
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. | |
Returns an array containing a slice of the input array, as determined by starting and ending index parameters. | |
Sorts array values in the specified column, array literal, or function that returns an array in ascending or descending order. | |
Combines one array containing keys and another array containing values into an Object of key-value pairs. | |
Generates an array of all unique elements among one or more arrays. | |
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. | |
Filters the keys and values from an Object data type column based on a specified key value. | |
Extracts the key values from an Object data type column and stores them in an array of String values. | |
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. | |
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. | |
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. | |
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. | |
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. | |
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. | |
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. |
Type Functions
Item | Description |
---|---|
The | |
The | |
The | |
The | |
The | |
The | |
The | |
Tests whether a set of values is not valid for a specified data type. | |
Tests whether a set of values is valid for a specified data type and is not a null value. | |
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. | |
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. | |
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. | |
Evaluates a String input against the Array datatype. If the input matches, the function outputs an Array value. Input can be a literal, a column of values, or a function returning String values. | |
Evaluates a String input against the Object datatype. If the input matches, the function outputs an Object value. Input can be a literal, a column of values, or a function returning String values. | |
Evaluates an input against the String datatype. If the input matches, the function outputs a String value. Input can be a literal, a column of values, or a function returning values. Values can be of any data type. |
Window Functions
Item | Description |
---|---|
Extracts the value from a column that is a specified number of rows before the current value. | |
Extracts the value from a column that is a specified number of rows after the current value. | |
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. | |
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. | |
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. | |
Computes the rolling average of values forward or backward of the current row within the specified column. | |
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. | |
Computes the rolling maximum of values forward or backward of the current row within the specified column. Inputs can be Integer, Decimal, or Datetime. | |
Computes the rolling minimum of values forward or backward of the current row within the specified column. Inputs can be Integer, Decimal, or Datetime. | |
Computes the rolling mode (most common value) forward or backward of the current row within the specified column. Input values must be of Datetime data type. | |
Computes the rolling maximum of date values forward or backward of the current row within the specified column. Inputs must be of Datetime type. | |
Computes the rolling minimum of Date values forward or backward of the current row within the specified column. Inputs must be of Datetime type. | |
Computes the rolling sum of values forward or backward of the current row within the specified column. | |
Computes the rolling standard deviation of values forward or backward of the current row within the specified column. | |
Computes the rolling standard deviation of values forward or backward of the current row within the specified column using the sample statistical method. | |
Computes the rolling variance of values forward or backward of the current row within the specified column. | |
Computes the rolling variance of values forward or backward of the current row within the specified column using the sample statistical method. | |
Computes the rolling count of non-null values forward or backward of the current row within the specified column. | |
Computes the rolling kth largest value forward or backward of the current row. Inputs can be Integer, Decimal, or Datetime. | |
Computes the rolling unique kth largest value forward or backward of the current row. Inputs can be Integer, Decimal, or Datetime. | |
Computes the rolling list of values forward or backward of the current row within the specified column and returns an array of these values. | |
Generates a new column containing the row number as sorted by the | |
Generates a new session identifier based on a sorted column of timestamps and a specified rolling timeframe. |
Other Functions
Item | Description |
---|---|
Function returns the first non-missing value found in an array of columns. | |
The | |
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. | |
The | |
Returns the row number of the current row as it appeared in the original source dataset before any steps had been applied. | |
The | |
The | |
Ternary operators allow you to build if/then/else conditional logic within your transforms. Please use the | |
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. | |
Computes a four-octet internet protocol (IP) address from a 32-bit integer input. | |
Computes an array of integers, from a beginning integer to an end (stop) integer, stepping by a third parameter. | |
Finds the host value from a valid URL. Input values must be of URL or String type and can be literals or column references. | |
Finds the value for the domain from a valid URL. Input values must be of URL or String type. | |
Finds the value a subdomain value from a valid URL. Input values must be of URL or String type. | |
Finds the suffix value after the domain from a valid URL. Input values must be of URL or String type. | |
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. |