Language Index

Contents:

Expand all Collapse all

This section contains an index to all of the objects 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.
ANYIF Function	Selects a single non-null value from rows in each group that meet a specific condition.
AVERAGE Function	Computes the average (mean) from all row values in a column. Input column can be of Integer or Decimal.
AVERAGEIF Function	Generates the average value of rows in each group that meet a specific condition. Generated value is of Decimal type.
COUNTA Function	Generates the count of non-null rows in a specified column, optionally counted by group. Generated value is of Integer type.
COUNTAIF Function	Generates the count of non-null values for rows in each group that meet a specific condition.
COUNTDISTINCT Function	Generates the count of distinct values in a specified column, optionally counted by group. Generated value is of Integer type.
COUNTDISTINCTIF Function	Generates the count of distinct non-null values for rows in each group that meet a specific condition.
COUNT Function	Generates the count of rows in the dataset. Generated value is of Integer type.
COUNTIF Function	Generates the count of rows in each group that meet a specific condition. Generated value is of Integer type.
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.
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.
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.
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.
LISTIF Function	Returns list of all values in a column for rows that match a specified condition.
MAX Function	Computes the maximum value found in all row values in a column. Input column are permitted to be of Integer or Decimal.
MAXIF Function	Generates the maximum value of rows in each group that meet a specific condition.
MIN Function	Computes the minimum value found in all row values in a column. Input column can be of Integer or Decimal.
MINIF Function	Generates the minimum value of rows in each group that meet a specific condition.
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 or Decimal type.
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 or Decimal type.
STDEV Function	Computes the standard deviation across all column values of Integer or Decimal type.
STDEVIF Function	Generates the standard deviation of values by group in a column that meet a specific condition.
SUM Function	Computes the sum of all values found in all row values in a column. Input column can be of Integer or Decimal.
SUMIF Function	Generates the sum of rows in each group that meet a specific condition.
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.
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`.
VARIF Function	Generates the variance of values by group in a column that meet a specific condition.
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 or Decimal.

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`.
AND Function	Returns `true` if both arguments evaluate to `true`. Equivalent to the `&&` operator.
OR Function	Returns `true` if either argument evaluates to `true`. Equivalent to the `\|\|` operator.
NOT Function	Returns `true` if the argument evaluates to `false`, and vice-versa. Equivalent to the `!` operator.

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.
ISEVEN Function	Returns `true` if the argument is an even value. Argument can be an Integer, a function returning Integers, or a column reference.
ISODD Function	Returns `true` if the argument is an odd value. Argument can be an Integer, a function returning Integers, or a column reference.
IN Function	Returns `true` if the first parameter is contained in the array of values in the second parameter.
MATCHES Function	Returns `true` if a value contains a string or pattern. The value to search can be a string literal or a reference to a column of String type.
EQUAL Function	Returns `true` if the first argument is equal to the second argument. Equivalent to the `=` operator.
NOTEQUAL Function	Returns `true` if the first argument is not equal to the second argument. Equivalent to the `<>` or `!=` operator.
GREATERTHAN Function	Returns `true` if the first argument is greater than but not equal to the second argument. Equivalent to the `>` operator.
GREATERTHANEQUAL Function	Returns `true` if the first argument is greater than or equal to the second argument. Equivalent to the `>=` operator.
LESSTHAN Function	Returns `true` if the first argument is less than but not equal to the second argument. Equivalent to the `<` operator.
LESSTHANEQUAL Function	Returns `true` if the first argument is less than or equal to the second argument. Equivalent to the `<=` operator.

Math Functions

Item	Description
Numeric Operators	Numeric operators enable you to generate new values based on a computation (e.g. `3 + 4`).
NUMFORMAT Function	Formats a numeric set of values according to the specified number formatting. Source values can be a reference to a column containing Integer or Decimal values.
ADD Function	Returns the value of summing the first argument and the second argument. Equivalent to the `+` operator.
SUBTRACT Function	Returns the value of subtracting the second argument from the first argument. Equivalent to the `-` operator.
MULTIPLY Function	Returns the value of multiplying the first argument by the second argument. Equivalent to the `*` operator.
DIVIDE Function	Returns the value of dividing the first argument by the second argument. Equivalent to the `/` operator.
MOD Function	Returns the modulo value, which is the remainder of dividing the first argument by the second argument. Equivalent to the `%` operator.
NEGATE Function	Returns the opposite of the value that is the first argument. Equivalent to the `-` operator placed in front of the argument.
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.
LCM Function	Returns the least common multiple shared by the first and second arguments.
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.
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.
LOG Function	Computes the logarithm of the first argument with a base of the second argument.
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.
POW Function	Computes the value of the first argument raised to the value of the second argument.
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.
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.
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.
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.
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.
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.
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.

Date Functions

Item	Description
DATE Function	Generates a date value from three inputs of Integer type: year, month, and day.
TIME Function	Generates time values from three inputs of Integer type: hour, minute, and second.
DATETIME Function	Generates a Datetime value from the following inputs of Integer type: year, month, day, hour, minute, and second.
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.
DATEDIF Function	Calculates the difference between two valid date values for the specified units of measure.
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.
UNIXTIMEFORMAT Function	Formats a set of Unix timestamps according to a specified date formatting string.
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.
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.
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.
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.
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.
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.
HOUR Function	Derives the hour value from a Datetime value. Generated hours are expressed according to the 24-hour clock.
MINUTE Function	Derives the minutes value from a Datetime value. Minutes are expressed as integers from 0 to 59.
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.
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.
NOW Function	Derives the timestamp for the current time in UTC time zone. You can specify a different time zone by optional parameter.
TODAY Function	Derives the value for the current date in UTC time zone. You can specify a different time zone by optional parameter.

String Functions

Item	Description
CHAR Function	Generates the Unicode character corresponding to an inputted Integer value.
UNICODE Function	Generates the Unicode index value for the first character of the input string.
UPPER Function	All alphabetical characters in the input value are converted to uppercase in the output value.
LOWER Function	All alphabetical characters in the input value are converted to lowercase in the output value.
PROPER Function	Converts an input string to propercase. Input can be a column reference or a string literal.
TRIM Function	Removes leading and trailing whitespace from a string. Spacing between words is not removed.
REMOVEWHITESPACE Function	Removes all whitespace from a string, including leading and trailing whitespace and all whitespace within the string.
REMOVESYMBOLS Function	Removes all characters from a string that are not letters, numbers, accented Latin characters, or whitespace.
LEN Function	Returns the number of characters in a specified string. String value can be a column reference or string literal.
FIND Function	Returns the index value in the input string where a specified matching string is located. Search is conducted left-to-right.
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.
SUBSTRING Function	Matches some or all of a string, based on the user-defined starting and ending index values within the string.
SUBSTITUTE Function	Replaces found string literal or pattern from a source with a string.
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.
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.
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.
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.
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 Alteryx pattern, regular expression, or a string.
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 Alteryx pattern, regular expression, or a string.
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.
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.
STRINGGREATERTHAN Function	Returns `true` if the first string evaluates to be greater than the second string, based on a set of common collation rules.
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.
STRINGLESSTHAN Function	Returns `true` if the first string evaluates to be less than the second string, based on a set of common collation rules.
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.
DOUBLEMETAPHONE Function	Returns a two-element array of primary and secondary phonetic encodings for an input string, based on the Double Metaphone algorithm.
DOUBLEMETAPHONEEQUALS Function	Compares two input strings using the Double Metaphone algorithm. An optional threshold parameter can be modified to adjust the tolerance for matching.
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.

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.
ARRAYCROSS Function	Generates a nested array containing the cross-product of all elements in two or more arrays.
ARRAYELEMENTAT Function	Computes the 0-based index value for an array element in the specified column, array literal, or function that returns an array.
ARRAYINDEXOF Function	Computes the index at which a specified element is first found within an array. Indexing is left to right.
ARRAYINTERSECT Function	Generates an array containing all elements that appear in multiple input arrays, referenced as column names or array literals.
ARRAYLEN Function	Computes the number of elements in the arrays in the specified column, array literal, or function that returns an array.
ARRAYMERGEELEMENTS Function	Merges the elements of an array in left to right order into a string. Values are optionally delimited by a provided delimiter.
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.
ARRAYSLICE Function	Returns an array containing a slice of the input array, as determined by starting and ending index parameters.
ARRAYSORT Function	Sorts array values in the specified column, array literal, or function that returns an array in ascending or descending order.
ARRAYSTOMAP Function	Combines one array containing keys and another array containing values into an Object of key-value pairs.
ARRAYUNIQUE Function	Generates an array of all unique elements among one or more arrays.
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.
FILTEROBJECT Function	Filters the keys and values from an Object data type column based on a specified key value.
KEYS Function	Extracts the key values from an Object data type column and stores them in an array of String values.
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.
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.
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.
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.
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.
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.
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.

Type Functions

Item	Description
NULL Function	The `NULL` function generates null values.
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.
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.
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.
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.
ISNULL Function	The `ISNULL` function tests whether a column of values contains null values. For input column references, this function returns `true` or `false`.
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`.
ISMISMATCHED Function	Tests whether a set of values is not valid for a specified data type.
VALID Function	Tests whether a set of values is valid for a specified data type and is not a null value.

Window Functions

Item	Description
PREV Function	Extracts the value from a column that is a specified number of rows before the current value.
NEXT Function	Extracts the value from a column that is a specified number of rows after the current value.
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.
ROLLINGAVERAGE Function	Computes the rolling average of values forward or backward of the current row within the specified column.
ROLLINGMODE Function	Computes the rolling mode (most common value) forward or backward of the current row within the specified column.
ROLLINGMAX Function	Computes the rolling maximum of values forward or backward of the current row within the specified column.
ROLLINGMIN Function	Computes the rolling minimum of values forward or backward of the current row within the specified column.
ROLLINGSUM Function	Computes the rolling sum of values forward or backward of the current row within the specified column.
ROLLINGSTDEV Function	Computes the rolling standard deviation of values forward or backward of the current row within the specified column.
ROLLINGVAR Function	Computes the rolling variance of values forward or backward of the current row within the specified column.
ROLLINGCOUNTA Function	Computes the rolling count of non-null values forward or backward of the current row within the specified column.
ROLLINGKTHLARGEST Function	Computes the rolling kth largest value forward or backward of the current row.
ROLLINGKTHLARGESTUNIQUE Function	Computes the rolling unique kth largest value forward or backward of the current row.
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.
ROWNUMBER Function	Generates a new column containing the row number as sorted by the `order` parameter and optionally grouped by the `group` parameter.
SESSION Function	Generates a new session identifier based on a sorted column of timestamps and a specified rolling timeframe.

Other Functions

Item	Description
COALESCE Function	Function returns the first non-missing value found in an array of columns.
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.
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.
PI Function	The `PI` function generates the value of pi to 15 decimal places: 3.1415926535897932.
SOURCEROWNUMBER Function	Returns the row number of the current row as it appeared in the original source dataset before any steps had been applied.
IF Function	The `IF` function allows you to build if/then/else conditional logic within your transforms.
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.
Ternary Operators	Ternary operators allow you to build if/then/else conditional logic within your transforms. Please use the `IF` function instead.
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.
IPFROMINT Function	Computes a four-octet internet protocol (IP) address from a 32-bit integer input.
RANGE Function	Computes an array of integers, from a beginning integer to an end (stop) integer, stepping by a third parameter.
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.
DOMAIN Function	Finds the value for the domain from a valid URL. Input values must be of URL or String type.
SUBDOMAIN Function	Finds the value a subdomain value from a valid URL. Input values must be of URL or String type.
SUFFIX Function	Finds the suffix value after the domain from a valid URL. Input values must be of URL or String type.
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.

Send Feedback

Page tree