Workflow Functions Reference
These function types are available for transforming your data in Live Query. The type of data determines the functions you can use.
Visit Supported Data Types for more information.
Use this guide to quickly reference functions that you can use in the Expression Editor in Live Query. If you need more information and examples, visit the category-specific pages linked in each section.
Conditional
Conditional functions let you perform an action or calculation using an IF statement. For parameters and examples, go to Workflow Conditional Functions.
IF condition THEN t ELSE f ENDIF
IF c THEN t ELSE f ENDIF: Returns t if the condition c is true, else returns f.
IF c THEN t1 ELSEIF c2 THEN t2 ELSE f ENDIF
IF c THEN t1 ELSEIF c2 THEN t2 ELSE f ENDIFIF c THEN t ELSE f ENDIF: Returns t1 if c is true, else returns t2 if condition c2 is true, else returns f.
IIF
IIF(bool, x, y): Returns x if bool is true, else returns y.
Switch
Switch(Value,Default,Case1,Result1,...,CaseN,ResultN): Compares a value against a list of cases and returns the corresponding result.
Conversion
Conversion functions convert numbers to strings or strings to numbers. For parameters and examples, go to Workflow Conversion Functions.
CharFromInt
CharFromInt(x): Returns the Unicode® character that matches the input number x.
Note
This function isn't available for Live Query for Databricks.
ToDegrees
ToDegrees(x): Converts a numeric radian value (x) to degrees via the (x)rad × 180/π calculation. Please note that x must be a numeric value and can’t include any radian symbol (rad).
ToNumber
ToNumber(x, [bIgnoreErrors], [keepNulls], [decimalSeparator]): Converts a string (x), to a number.
ToRadians
ToRadians(x): Converts a numeric degree value (x) to radians via the (x)° × π/180 calculation. Please note that x must be a numeric value and can’t include the degree symbol (°).
ToString
ToString(x, numDec, [addThousandsSeparator], [decimalSeparator]): Converts a numeric parameter (x) to a string using numDec decimal places. The default selection uses a period as the decimal separator.
DateTime
DateTime functions let you perform an action or calculation on a date and time value. For parameters and examples, go to Workflow DateTime Functions.
DateTimeAdd
DateTimeAdd(dt,i,u): Adds a specific interval to a date-time value.
DateTimeDay
DateTimeDay(dt): Returns the numeric value for the day of the month in a date-time value.
DateTimeDiff
DateTimeDiff(dt1,dt2,u): Subtracts the second argument from the first and returns it as an integer difference. The duration is returned as a number, not a string, in the specified time units.
DateTimeFirstOfMonth
DateTimeFirstOfMonth(): Returns the first day of the month, at midnight.
DateTimeFormat
DateTimeFormat(dt, f): Converts date-time data df to another specified format f.
Note
This function isn't available for Live Query for Databricks.
DateTimeHour
DateTimeHour(dt): Returns the hour portion of the time in a date-time value.
DateTimeLastOfMonth
DateTimeLastOfMonth(): Returns the last day of the current month, with the clock set to one second before the end of the day (23:59:59).
DateTimeMinutes
DateTimeMinutes(dt): Returns the minutes portion of the time in a date-time value.
DateTimeMonth
DateTimeMonth(dt): Returns the numeric value for the month in a date-time value.
DateTimeNow
DateTimeNow(): Returns the current system date and time.
DateTimeParse
DateTimeParse(dt, f): Converts a date string with the specified format and language to the standard ISO format (yyyy-mm-dd HH:MM:SS).
Note
This function isn't available for Live Query for Databricks.
DateTimeQuarter
DateTimeQuarter(dt,[Q1Start]): Returns the numeric value for the quarter of the year in which a date-time (YYYY-MM-DD) value falls. Use the optional numeric parameter to indicate the start month for the first quarter (Q1).
DateTimeSeconds
DateTimeSeconds(dt): Returns the seconds portion of the time in a date-time value.
DateTimeToday
DateTimeToday(): Returns today’s date.
DateTimeTrim
DateTimeTrim(dt, f): Removes unwanted portions of a date-time value and returns the modified date-time value.
Note
This function isn't available for Live Query for Databricks.
DateTimeYear
DateTimeYear(dt): Returns the numeric value for the year in a date-time value.
ToDate
ToDate(x): Converts a string, number, or date-time to a date.
ToDateTime
DateTimeTrim(ToDateTime(x), t): Converts a string, number, or date value to a date-time.
Finance
A finance function applies financial algorithms or mathematical calculations. To learn more. go to Workflow Finance Functions.
FinanceCAGR
FinanceCAGR(BeginningValue, EndingValue, NumYears): Calculates Compound Annual Growth Rate: The geometric mean growth rate on an annualized basis.
FinanceEffectiveRate
FinanceEffectiveRate(NominalRate, PaymentsPerYear): Calculates Effective Annual Interest Rate: The interest rate on a loan or financial product restated from the nominal interest rate as an interest rate with an annual compound interest payable in arrears.
FinanceFV
FinanceFV(Rate, NumPayments, PaymentAmount, PresentValue, PayAtPeriodBegin): Calculates Future Value of an Investment: The value of an asset at a specified time in the future, assuming a certain interest rate or rate of return.
FinanceIRR
FinanceIRR(Value1, Value2): Calculates Internal Rate of Return, which is the interest rate at which the costs of the investment lead to the benefits of the investment. This means that all gains from the investment are inherent in the time value of money and that the investment has a zero net present value at this interest rate.
Note
This function isn't available for Live Query for Databricks.
FinanceNominalRate
FinanceNominalRate(EffectiveRate, PaymentsPerYear): Calculates Nominal Annual Interest Rate: An interest rate is called nominal if the frequency of compounding (such as a month) is not identical to the basic time unit (normally a year).
FinanceNPER
FinanceNPER(Rate, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin): Calculates the Number of periods for an investment or loan.
FInancePMT
FinancePMT(Rate, NumPayments, PresentValue, FutureValue, PayAtPeriodBegin): Calculates payments on a loan.
FinancePV
FinancePV(Rate, NumPayments, PaymentAmount, FutureValue, PayAtPeriodBegin): Calculates Present Value of an investment: The value on a given date of a future payment, or series of future payments, discounted to reflect the time value of money and other factors such as investment risk.
FinanceXIRR
FinanceXIRR(Value1, Date1, Value2, Date2): Calculates the Internal Rate of Return of an investment with dates.
Note
This function isn't available for Live Query for Databricks.
FinanceXNPV
FinanceXNPV(Rate, Value1, Date1, Value2, Date2): Calculates the Net Present Value of an investment with dates.
Math
A math function performs mathematical calculations. For parameters and examples, go to Workflow Math Functions.
ABS
ABS(x): Returns the absolute value of x. It is the distance between 0 and a number. The value is always positive.
ACOS
ACOS(x): Returns the arccosine, or inverse cosine, of a number (x). The arccosine is the angle whose cosine is (x).
ASIN
ASIN(x): Returns the arcsine, or inverse sine, of a number (x). The arcsine is the angle whose sine is (x).
ATAN
ATAN(x): Returns the arctangent, or inverse tangent, of a number (x). The arctangent is the angle whose tangent is (x). The returned angle is given in radians in the range -pi/2 to pi/2.
ATAN2
ATAN2(y, x): Returns the arctangent, or inverse tangent, of y and x (Arc - Tangent of (y/x)).
Average
Average(n1, ...): Returns the average of a list of numbers. NULL values are treated as 0 and taken into consideration in the calculation of the average value.
AverageNonNull
AverageNonNull(n1, ...): Returns the average of a list of numbers. Null values are excluded from the calculation. The calculation only works with Numeric data types.
CEIL
.CEIL(x, [mult]): Returns a numeric value (x) raised to the lowest multiple of (mult) that is greater than or equal to x. The (mult) parameter must be a number greater than 0. If (mult) is not specified, the function returns the smallest integer greater than or equal to (x).
COS
COS(x): Returns the cosine of a given angle (x). x must be in radians.
EXP
EXP(x): Returns e raised to the x power.
FLOOR
FLOOR(x, [mult]): Returns a numeric value (x) lowered to the highest multiple of (mult) that is less than or equal to x. The (mult) parameter must be a number greater than 0. If (mult) is not specified, the function returns the largest integer less than or equal to (x), as shown in the first example below.
LOG
LOG(x): Returns the natural logarithm of a number (x). x should be a positive number.
LOG10
LOG10(x): Returns the base-10 logarithm of a number (x). x should be a positive number.
Median
Median(...): Calculates the median of 1 or more (possibly unsorted) values.
Note
This function isn't available for Live Query for Databricks.
Mod
Mod(n,d): Modulo of a number (n) divided by a number (d) (integer operation).
PI
PI(): Returns the value of the constant PI to 15 digits of accuracy.
POW
POW(x, e): Returns a number (x) raised to the (e) power.
RAND
RAND(): Returns a random number greater than or equal to 0 and less than 1.
RandInt
RandInt(n): Returns a random integer between 0 and the specified parameter (n).
Round
Round(x, mult): Returns x rounded to the nearest multiple of the number specified in mult.
SIN
SIN(x): Returns the sine of a given angle (x). x must be in radians.
SmartRound
SmartRound(x): Returns a number (x) rounded to the nearest multiple of a value determined dynamically based on the size of (x).
SQRT
SQRT(x): Returns the square root of a number (x).
TAN
TAN(x): Returns the tangent of a given angle (x). x must be in radians.
TANH
TANH(x): Returns the hyperbolic tangent of a number (x).
Min/Max
A minimum or maximum function finds the smallest and largest value of a set of values. To learn more, go to Workflow Min/Max Functions.
Between
BETWEEN(x, min, max): Tests if a value (x) is between 2 other values (min and max), inclusive of min and max. Returns TRUE if x is between min and max. Returns FALSE if x is not between min and max.
Bound
Bound(x, min, max): If (x < min) returns min, else if (x > max) return max, else returns (x).
Max
Max(v0, v1,..., vn): Returns the maximum value from the list.
Min
Min(v0, v1,..., vn): Returns the minimum value from the list.
Operators
An operator is a character that represents an action. Use an arithmetic operator to perform mathematical calculations or a Boolean operator to work with true/false values. To learn more, go to Workflow Operators.
Addition
+: Addition.
Boolean AND - Keyword
AND: Combines 2 boolean values. The result is True if both of the combined values are True, False if either of them is False but not NULL, and NULL if either of them is NULL.
Boolean AND
&&: Combines 2 boolean values. The result is True if both of the combined values are True, False if either of them is False but not NULL, and NULL if either of them is NULL.
Boolean NOT - Keyword
NOT: Accepts 1 input. If that input is True, it returns False. If that input is False, it returns True. If that input is NULL, it returns NULL.
Boolean NOT
!: Accepts 1 input. If that input is True, it returns False. If that input is False, it returns True. If that input is NULL, it returns NULL.
Boolean OR - Keyword
OR: Combines 2 boolean values. The result is True if either (or both) of the two values is true. Returns NULL if any of the values is NULL.
Boolean OR
||: Combines 2 boolean values. The result is True if either (or both) of the two values is true. Returns NULL if any of the values is NULL.
Division
/: Division.
Equal To
=: Equal to.
Greater Than
>: Greater than.
Greater Than or Equal
>=: Greater than or equal.
Less Than
<: Less than.
Less Than or Equal
<=: Less than or equal.
Multiplication
*: Multiplication.
Not Equal To
!=: Not Equal To
Subtraction
-: Subtraction.
Value IN (...)
IN: Test value in list.
Value NOT IN (...)
NOT IN: Test value not in list.
Specialized
These functions perform a variety of specialized actions and can be used with all data types. For parameters and examples, go to Workflow Specialized Functions.
NULL
NULL(): Returns a Null value.
String
A string function performs operations on text data. Use a string function to cleanse data, convert data to a different format or case, compute metrics about the data, or perform other manipulations. For parameters and examples,go to Workflow String Functions.
Contains
Contains(String, Target, CaseInsensitive=1): Searches for the occurrence of a particular string within a string. Returns True if (String) contains (Target), else returns False.
CountWords
CountWords(string): Returns the count of words in the specified string. Words are defined by characters separated by a space.
Note
This function isn't available for Live Query for Databricks.
EndsWith
EndsWith(String, Target, CaseInsensitive=1): Checks if a string ends with a particular string. Returns True if (String) ends with (Target), else returns False. It defaults to case-insensitive.
FindNth
FindNth(Initial String, Target, Instance): Finds the instance (nth occurrence) of a target string in the initial string and returns the 0-indexed position of the instance. The function is case-sensitive and doesn’t accept negative parameters.
FindString
FindString(String,Target): Searches for the occurrence of a particular string (Target) within another string (String) and returns the numeric position of its occurrence in the string. Returns the 0-based index of the first occurrence of (Target) in (String). Returns -1 if no occurrence.
GetLeft
GetLeft(String, Delimiter): Returns the left part of the provided (String) until the first instance of the specified 1 or more (Delimiter).
GetPart
GetPart(String, Delimiter, Index): Uses the specified 1 or more (Delimiters) to divide (String) into substrings, and returns the substring in the position that is specified in (Index).
GetRight
GetRight(String, Delimiter): Returns the right part of the provided (String) after the first instance of the specified 1 or more (Delimiters).
GetWord
GetWord(string, n): Returns the Nth (0-based) word in the string. Words are defined as a collection of characters separated by a space. 0-based index, means the first word is at the 0 position.
Left
Left(String, len): Returns the first (len) characters of the string (String). If len is less than 0 or greater than the length of String, String remains unchanged.
Length
Length(String): Returns the length of the string (String).
LowerCase
LowerCase(String): Converts a string to lower case.
PadLeft
PadLeft (String, len, char): Pads the string to the left with the specified character to the specified length. If the padding (char) is more than 1 character long, only the first character is used.
PadRight
PadRight (String, len, char): Pads the string to the right with the specified character to the specified length. If the padding (char) is more than 1 character long, only the first character is used.
REGEX_CountMatches
REGEX_CountMatches(String,pattern,icase): Returns the count of matches within the string to the pattern.
Note
This function isn't available for Live Query for Databricks.
REGEX_Match
REGEX_Match(String,pattern,icase): Searches a string for an occurrence of a regular expression. Tells if the string matches the pattern from the first character to the end.
Note
This function isn't available for Live Query for Databricks.
REGEX_Replace
REGEX_Replace(String, pattern, replace, icase): Returns the string resulting from the RegEx find pattern and replace string.
Note
This function isn't available for Live Query for Databricks.
Replace
Replace(String, Target, Replacement): Returns the string (String) after replacing each occurrence of the String (Target) with the String (Replacement).
ReplaceChar
ReplaceChar(String, y, z): Returns the string (String) after replacing each occurrence of the character (y) with the character (z). If the replacement character (z) is a string with more than 1 character, only the first character is used. If (z) is empty, each character (String) that matches any character in (y) is simply removed.
ReplaceFirst
ReplaceFirst(String, Target, Replacement): Returns the string (String) after replacing the first occurrence of the string (Target) with the string (Replacement).
ReverseString
ReverseString(String): Reverses all the characters in the string.
Note
This function isn't available for Live Query for Databricks.
Right
Right(String, len): Returns the last (len) characters of the string. If len is less than 0 or greater than the length of String, the string remains unchanged.
StartsWith
StartsWith(String, Target, CaseInsensitive=1): Checks if a string starts with a particular string. Returns True if String starts with a particular string Target, else returns False.
STRCSPN
STRCSPN(String, y): Returns the length of the initial segment of the string (String) consisting of charactersnotin the string (y).
StripQuotes
StripQuotes(String): Removes a matched set of quotation marks or apostrophes from the ends of the string.
STRSPN
STRSPN(String, y): Returns the length of the initial segment of the string (String) consisting of characters in the string (y).
Substring
Substring(String, start, length): Returns the substring of (String) starting at (start), stopping after (length), if provided.
TitleCase
TitleCase(String): Converts a string to title case.
Trim
Trim(String, y): Removes the character(s) in the string (y) from the ends of the string (String). Y is optional and defaults to trimming white space. Notice in the TRIM function examples the specified characters are trimmed. It doesn't matter what order the characters are in.
TrimLeft
TrimLeft(String, y): Removes character in the string (y) from the beginning of the string (String). Y is optional and defaults to trimming white space.
TrimRight
TrimRight(String, y): Removes character in the string (y) from the end of the string (String). Y is optional and defaults to trimming white space.
Uppercase
Uppercase(String): Converts a string to uppercase.
Test
A test function performs data comparisons. Use a test function to identify the data type of a value or determine if a value exists. For parameters and examples, go to Workflow Test Functions.
CompareDigits
CompareDigits(a, b, nNumDigits): Compares 2 numbers and determines if they are the same to the given number of digits (nNumDigits).
CompareEpsilon
CompareEpsilon(a, b, epsilon): Compares 2 floating-point numbers and returns True if they are within epsilon.
EqualStrings
EqualStrings(a,b): Compares string (a) to string (b) to determine if they are an exact match. The function returns True if all characters in both a and b are identical and False if 1 or more characters are not identical.
IsEmpty
IsEmpty(v): Tests if v is NULL or equal to "".
IsInteger
IsInteger(v): Tests if (v) contains a value that can be converted to an integer. If so, it returns True.
IsLowerCase
IsLowerCase(String): Returns True if all (1 or more) alphabetic characters in a string are lowercase and False if 1 or more alphabetic characters are uppercase. The function ignores non-alphabetic characters. Note that this function only applies to characters with a case distinction and might not apply to characters in all languages.
IsNull
IsNull(v): Tests if (v) is NULL. Returns True if v is NULL, otherwise returns False.
IsNumber
IsNumber(v): Tests if the field type for (v) is a number or not.
IsString
IsString(v): Tests if the field type for (v) is a string or not.
IsUpperCase
IsUpperCase(String): Returns True if all (1 or more) alphabetic characters in a string are uppercase and False if 1 or more alphabetic characters are lowercase. The function ignores non-alphabetic characters. Note that this function only applies to characters with a case distinction and might not apply to characters in all languages.