Formulas

ABS(n)

n is a number or field that you want to determine the absolute value of.

CEIL(n)

n is a number or field that you want to round up to the nearest integer.

EXP(n)

n is a number or field to which you want to raise e.

FLOOR(n)

n is a number or field that you want to round down to the nearest integer.

LOG(m, n)

• m is a number or field used as the base of the logarithm.
• n is a number or field that you want to take the logarithm of.

MAX(value1, value2)

• value1 is the first number or field to be compared.
• value2 is the second number or field to be compared.

MIN(value1, value2)

• value1 is the first number or field to be compared.
• value2 is the second number or field to be compared.

MOD(number, divisor)

• number is the number or field to be divided.
• divisor is the number or field that you want to divide by.

POWER(m, n)

• m is a number or field that you want to raise to the specified power.
• n is a number or field that is the power that you want to raise the specified number to.

ROUND(n[, m])

• n is a number or field that you want to round.
• m is a number or field that specifies the number of digits to round the specified number to.

SQRT(n)

n is a number or field that you want to find the square root of.

TRUNC(number, decimalPlaces)

• number is a number or field that you want to truncate.
• decimalPlaces is the number of digits you want to the right of the decimal point. This parameter is required. If you don’t specify want any decimal values, use a 0.

BEGINS(string, prefix)

• string is the text or field to search.
• prefix is the value to search for at the beginning of the field.

CONCAT(field, num_chars)

• field is the text or field from which you want to extract characters.
• num_chars is the number of characters you want to return.

CONTAINS(string, searchString)

• string is the text or field that you want to search within.
• searchString is the specific text you are looking for inside the string.

ENDS(string, prefix)

• string is the text or field to search.
• prefix is the value to search for at the end of the field.

LENGTH(field)

field is the text or field to measure.

LOWER(string)

string is the text or field to convert to lowercase.

LTRIM(string, substr)

• string is the text or field to remove the leading spaces or specified characters from.
• substr is the value to remove from the string. If unspecified, the transformation removes the leading spaces.

Returns a 32-character string that is a text representation of the hexadecimal value of a 128-bit checksum.

MD5(string))

string is the text or field for which you want to calculate the checksum.

RTRIM(string, substr)

• string is the text or field to remove the trailing spaces or specified characters from.
• substr is the value to remove from the string. If unspecified, the transformation removes the trailing spaces.

SUBSTITUTE(string, searchStr, replaceStr)

• string is the text or field that contains the string to replace.
• searchStr is the string to replace.
• replaceStr is the value to replace the string.

SUBSTR(field, pos[, len])

• field is the text or field to extract the substring from.
• position is the starting character position of the substring. The first character in a string is position 1. If the position is negative, the position is relative to the end of the string. A position of -1 is the last character.
• len is the number of characters to return. If the length is 0, the output is an empty string. If the length is negative, the function returns null. This parameter is optional.

TEXT(number)

number is the number or field to convert.

TRIM(string, substr)

• string is the text or field to remove the specified substring from.
• substr is the value removed from the string. This parameter is optional. If unspecified, the transformation removes the trailing spaces.

UPPER(string)

string is the text or field to convert to uppercase.

VALUE(string)

string is the text or field to convert to number.

ADDDAYS(date, 5)

• date is the date or field to add days to.
• 5 is the number of days to add

ADDMONTHS(date, 5)

• date is the date or field to add months to.
• 5 is the number of months to add to add

DATEDIFF(endDate, startDate)

• endDate is the later of the two dates you are finding the difference between.
• startDate is the earlier of the two dates you are finding the difference between.

DATETIMEVALUE(dateTimeString[,format])

• dateTimeStringfield is a timestamp or field that you want to convert.
• format is the timestamp format pattern to follow. This parameter is optional. The function follows casting rules to a timestamp if format isn’t included.

DATEVALUE(dateString[,format])

• dateStringfield is a date or field that you want to convert.
• format is the date format pattern to follow. This parameter is optional. The function follows casting rules to a timestamp if format isn’t included.

DAY(date)

date is the date field to extract the day of the month from.

MONTHDIFF(endDate, startDate)

• endDate is the later of the two dates you are finding the difference between.
• startDate is the earlier of the two dates you are finding the difference between.

WEEKDAY(date)

date is the date field to extract the day of the week from.

AND(logical1, logical2, ...)

• logical1 is the first logical condition that you want to check. This expression must result in a value of either TRUE or FALSE.
• logical2 is the second logical condition that you want to check, which must also result in a value of TRUE or FALSE.

BLANKVALUE(expression, substitute_expression)

• expression is the field or value you want to check to see if it's empty.
• substitute_expression is the value you want the formula to return if the expression is blank.

CASE(expression,value1, result1, value2, result2,...,else_result)

• expression is the field or value you want to compare against a series of other values.
• value1 is the first value you want to check for a match with the expression.
• result1 is the value that is returned if the expression matches value1.
• value2, result2, ... is the next pair of values to check and the corresponding results to return. You can have many of these pairs.
• else_result is the value that is returned if the expression does not match any of the specified values.

IF(logical_test, value_if_true, value_if_false)

• logical_test is the condition you want to check. This expression must result in a value of either TRUE or FALSE.
• value_if_true is the value that is returned if the logical_test is true.
• value_if_false is the value that is returned if the logical_test is false.

IF(logical_test, value_if_true, value_if_false)

• logical_test is the condition you want to check. This expression must result in a value of either TRUE or FALSE.
• value_if_true is the value that is returned if the logical_test is true.
• value_if_false is the value that is returned if the logical_test is false.

IF(logical_test, value_if_true, value_if_false)

• logical_test is the condition you want to check. This expression must result in a value of either TRUE or FALSE.
• value_if_true is the value that is returned if the logical_test is true.
• value_if_false is the value that is returned if the logical_test is false.

NULLVALUE(expression, substitute_expression)

• expression is the number, date, or datetime field you want to check to see if it is empty (null).
• substitute_expression is the value you want the formula to return if the expression is empty.

OR(logical1, logical1, ...)

• logical1 is the first logical condition that you want to check. This expression must result in a value of either TRUE or FALSE.
• logical2 is the second logical condition that you want to check, which must also result in a value of TRUE or FALSE.

Gets the first non-null value from a list of parameters, or replaces nulls with a different value.

This function is often used to substitute a default value for null values when data is retrieved for display.

COALESCE(expr1, expr2, ...)

• expression1 is the first column or text string to search for a non-null value.
• expression2 is the next column or text string to search for a non-null value.

EXPLODE(field)

field is the field that contains the multivalue data you want to transform into individual rows.

SEQUENCE(start, end, step)

• start is the beginning value of the series.
• end is the ending value of the series.
• step is the increment or interval between each value in the series.

ARRAYJOIN(field)

field is the multivalue field to be converted to a string.

COLLECTLIST(field)

field is the multivalue field whose values you want to collect into an array. The function will take the value from this field for each record in the defined group.

COLLECTSET(field)

field is the multivalue field whose values you want to collect into a unique array. The function will only store each distinct value from this field once.

CURRENT(field)

field is the field whose value you want to retrieve for the current row.

FIRSTVALUE(field)

field is the field whose value you want to retrieve from the first record in the partition.

LAG(field)

field is the field whose value you want to retrieve from a previous record.

LASTVALUE(field)

field is the field whose value you want to retrieve from the last record in the partition.

LEAD(field)

field is the field whose value you want to retrieve from a subsequent record.

SUM(field)

field is the number field whose values you want to add together.