Formulas for column values in Table Builder
You can use a predefined function and create a formula to calculate the value of a column without writing a script. Use a predefined function or create a nested formula by using the existing predefined functions to calculate the column value type.
Supported operators
The following comparison operators are supported only for number type values.
- = (Equal to)
- <> (Not equal to)
- > (Greater than)
- < (Less than)
- >= (Greater than or equal to)
- <= (Less than or equal to)
Parent Topic:Field configuration in Table Builder
Simple math functions
Use simple math functions to perform basic mathematical calculations on numeric value columns.
AVERAGE
Returns the average value of the arguments.
| Syntax | Input | Output |
|---|---|---|
| AVERAGE(argument 1, argument 2, … argument n) | Numeric value, function call, or variable | Numeric value |
Examples:
Function: AVERAGE(1,2,3)
The result is 2.
Formula: AVERAGE(LENGTH(first_name), LENGTH(last_name))
The result is the average value of the number of characters in the first_name column and last_name column.
DIVIDE
Returns the final quotient value after consecutively dividing the first argument with the next argument until the function reaches the last argument.
| Syntax | Input | Output |
|---|---|---|
| DIVIDE(argument 1, argument 2 … argument n) | Numeric value, function call, or variable | Numeric value |
Examples:
Function: DIVIDE(10,20, 0.25, 10)
The result is 0.2.
Formula: DIVIDE(LENGTH(full_name),2)
The result is the number of characters in the full_name column divided by 2.
INDEXMATCH
Retrieves the first not null value from the specified set of arguments.
| Syntax | Input | Output |
|---|---|---|
| INDEXMATCH\(argument 1, argument 2 , … argument n\) | String, numeric value, function call, or variable. | Numeric value |
Example:
Function: INDEXMATCH(""," ",2,"string")
The result is 2.
MAX
Returns the highest value in the specified arguments.
| Syntax | Input | Output |
|---|---|---|
| MAX(argument 1, argument 2, … argument n) | Numeric value, function call, or variable | Numeric value |
Examples:
Function: MAX(1, -5, 20, 6)
The result is 20.
Formula: MAX(LENGTH(first_name), LENGTH(last_name))
The result is the number of characters in the first_name column or last_name column whichever is the highest.
MIN
Returns the lowest value in the specified arguments.
| Syntax | Input | Output |
|---|---|---|
| MIN(argument 1, argument 2, … argument n) | Numeric value, function call, or variable | Numeric value |
Examples:
Function: MIN(1, -5, 20, 6)
The result is -5.
Formula: MIN(LENGTH(first_name), LENGTH(last_name))
The result is the number of characters in the first_name column or last_name column whichever is the lowest.
MULTIPLY
Returns the total multiplied value of the arguments.
| Syntax | Input | Output |
|---|---|---|
| MULTIPLY (argument 1, argument 2, … argument n) | Numeric value, function call, or variable | Numeric value |
Examples:
Function: MULTIPLY(12, 4)
The result is 48.
Formula: MULTIPLY(order, 2)
The result is the order column value multiplied by 2.
POWER
Returns the result of the base value raised to the power of the exponent value.
| Syntax | Input | Output |
|---|---|---|
| POWER\(argument 1, argument 2\) | argument 1 is base and argument 2 is exponent.- base: number or variable - exponent: number or variable | Number |
Examples:
Function: POWER(3,2)
The result is 9.
Formula: POWER(LENGTH(full_name),2)
The result is the number of characters in the full_name column to the power of 2.
SUBTRACT
Returns the result value after consecutively subtracting the next available argument from the earlier argument until the function reaches last argument.
| Syntax | Input | Output |
|---|---|---|
| SUBTRACT(argument 1, argument 2 … argument n) | Numeric value, function call, or variable | Numeric value |
Examples:
Function: SUBTRACT(1.15, 0.02, 0.45, -0.85)
The result is 1.53.
Formula: SUBTRACT(LENGTH(full_name), LENGTH(first_name))
The result is the number of characters from the full_name column minus the number of characters from the first_name column.
SUM
Returns the sum of all the arguments.
| Syntax | Input | Output |
|---|---|---|
| SUM(argument 1,argument 2, ... argument n) | Numeric value, function call, or variable | Numeric value |
Examples:
Function: SUM(0.03, -0.02, 1)
The result is 1.01.
Formula: SUM(LENGTH(first_name), LENGTH(last_name))
The result is the total number of characters in the first_name column plus the total number of characters in the last_name column.
COUNTIF
Returns the number of arguments that match the specified criteria within the specified set of arguments.
| Syntax | Input | Output |
|---|---|---|
| COUNTIF\(argument 1, argument 2, argument n-1, criteria\) | - argument 1 … argument n: String, numeric value, function call, or variable. - criteria: Criteria that evaluates the specified set of arguments. String, numeric value, function call, or variable. | Numeric value |
Example:
Function: COUNTIF(2,3,2,"string",2)
The result is 2.
MODE
Returns the most frequently repeating value in the specified set of arguments.
| Syntax | Input | Output |
|---|---|---|
| MODE(argument 1,argument 2, ... argument n) | Numeric value, function call, or variable | Numeric value |
Example:
Function: MODE(1, 2, 2, 3, 3, 3)
The result is 3.
String functions
Use string functions to reformat or perform calculations on string column values.
CONCATENATE
Joins one or more input strings into a single string.
| Syntax | Input | Output |
|---|---|---|
| CONCATENATE(string 1, string 2, … string n) | String, function call, or variable | String |
Examples:
Function: CONCATENATE(first_name, ".", last_name, "@", LOWERCASE(example), ".com")
The result is the concatenated value <first_name_value>.<last_name_value>@example.com. In this example, <first_name_value> and <last_name_value> are placeholders.
Function: CONCATENATE(first_name, " ", last_name)
The result is the concatenated string of first_name column value and last_name column value separated by a white space.
ISBLANK
Finds white spaces or blank values in the string and returns true if there are any.
| Syntax | Input | Output |
|---|---|---|
| ISBLANK(argument) | String or value | True or false |
Examples:
Function: ISBLANK(“example_string”)
The result is false.
Function: ISBLANK(full_name)
The result is true only when there are empty spaces in the full_name column. Otherwise, the result is false.
LENGTH
Returns the total number of characters in the input string.
| Syntax | Input | Output |
|---|---|---|
| LENGTH(argument) | String value, function call, or variable | Numeric value |
Examples:
Function: LENGTH("example_string")
The result is 14.
Function: LENGTH(full_name)
The result is the total number of characters in the full_name column value.
LOWERCASE
Converts the input string to all lowercase characters.
| Syntax | Input | Output |
|---|---|---|
| LOWERCASE(argument) | String, function call, or variable | String in lowercase |
Examples:
Function: LOWERCASE(“ExamPle inpuT stRing”)
The result is example input string.
Function: LOWERCASE(sys_created_by)
The result is the lower case string of the sys_created_by column value.
REPLACE
Replaces the characters in the source string with the characters in the target string.
| Syntax | Input | Output |
|---|---|---|
| REPLACE\(source\_string, target\_string, replacement\_string\) | - source\_string: String, function call, or variable - target\_string: String, function call, or variable - replacement\_string: String, function call, or variable | String |
Examples:
Function: REPLACE(“Pepperoni Pizza”, “Pepperoni”, “Cheese”)
The result string is Cheese Pizza.
Function: REPLACE("abe.tuter@example.com", "example", company_name)
The result string is abe.tuter@<company_name>.com. In this example, <company_name>is a place holder.
TITLECASE
Converts the input string to all title case characters.
| Syntax | Input | Output |
|---|---|---|
| TITLECASE(argument) | String, function call, or variable | String in title case |
Examples:
Function: TITLECASE("example string")
The result is Example String.
Function: TITLECASE(full_name)
The result is the full name column value in the title case.
UPPERCASE
Converts the input string to all uppercase characters.
| Syntax | Input | Output |
|---|---|---|
| UPPERCASE(argument) | String value, function call, or variable | String in uppercase |
Examples:
Function: UPPERCASE("eXamPle sTring")
The result is EXAMPLE STRING.
Function: UPPERCASE(state)
The result is the State column value in upper case.
FIND
Searches for the first occurrence of a substring within a string and returns the position of the first occurrence.
Note: This function is case sensitive.
| Syntax | Input | Output |
|---|---|---|
| FIND\(search\_string, source\_string, from\_index\) | - search\_string: Substring, function call, or variable. - source\_string: Main string, function call, or variable. - from\_index: Index position in the main string from where the search should start. Numeric value, function call, or variable. | Numeric value \(integer\). When the substring does not exist in the main string, -1 is returned. |
Example:
Function: FIND("morning", "Good morning")
The result is 5.
SEARCH
Searches for a substring within a string and returns the position of the first occurrence of the substring.
Note: This function is case insensitive.
| Syntax | Input | Output |
|---|---|---|
| SEARCH\(search\_string, source\_string, from\_index\) | - search\_string: Substring, function call, or variable. - source\_string: Main string, function call, or variable. - from\_index: Index position in the main string from where the search should start. Numeric value, function call, or variable. | Numeric value \(integer\). When the substring does not exist in the main string, -1 is returned. |
Examples:
SEARCH("Morning", "Good morning")
The result is 5.
SEARCH("World","Hello world!")
The result is -1.
SUBSTRING
Retrieves a substring from a string at the specified index position and for the specified length.
| Syntax | Input | Output |
|---|---|---|
| SUBSTRING\(source\_string, start\_index, length\) | - source\_string: String, function call, or variable. - start\_index: Position in the string from where the substring is extracted. Numeric value, function call, or variable. - length: Length of the substring that should be extracted. | String |
Example:
SUBSTRING("Hello, Good Morning", 7, 4)
The result substring is 'Good'.
Date and time functions
Use date and time functions to calculate or reformat the date and time column values.
NOW
Returns the current date and time of the instance in ISO format (YYYY-MM-DD hh:mm:ss).
| Syntax | Input | Output |
|---|---|---|
| NOW() | No arguments are required for this function | ISO format of current date and time |
Example:
Function: NOW()
The result is the current date and time in ISO format.
TODAY
Returns the current date with time offset to start of the day in ISO format in UTC time zone.
| Syntax | Input | Output |
|---|---|---|
| TODAY() | No arguments are required for this function | Current date with time offset to start of the day in ISO format. |
Example:
Function: TODAY()
The result is the current date and start time of the day in ISO format.
TIMEDIFF
Evaluates the time duration difference between two dates.
| Syntax | Input | Output |
|---|---|---|
| TIMEDIFF(argument1, argument2) | Date in ISO format (YYYY-MM-DD hh:mm:ss) as string or variable | Duration |
Examples:
Function: TIMEDIFF("2021-05-02 9:10:12", "2021-04-07 6:2:23")
The result is 25 03:07:49.
Formula: TIMEDIFF(sys_created_on, NOW())
The result is the time duration difference between the sys_created_on date and the current date of the system.
DATEDIF
Evaluates the difference between the two dates in days, months, or years.
| Syntax | Input | Output |
|---|---|---|
| DATEDIF\(start\_date, end\_date, date\_difference\_unit\) | - start\_date: Date in ISO format \(YYYY-MM-DD or YYYY-MM-DD hh:mm:ss\) as string or variable. - end\_date: Date in ISO format \(YYYY-MM-DD or YYYY-MM-DD hh:mm:ss\) as string or variable. - date\_difference\_unit: Character String and either "Y","M", or "D" in lowercase or uppercase. Default is "D". | Numeric duration value based on the specified date difference unit. |
Example:
Function: DATEDIF("2021-05-02 9:10:12", "2021-05-05 6:2:23 ","d")
The result is 3.
DATE
Creates a date from the specified individual year, month, and day values. The created date is in Coordinated Universal Time (UTC) time zone.
| Syntax | Input | Output |
|---|---|---|
| DATE\(year,month,day\) | - year: Numeric value, variable or function. - month: Numeric value, variable or function. - day: Numeric value, variable or function. | Date in ISO format \(YYYY-MM-DD hh:mm:ss\) |
Example:
Function: DATE(2021,5,2)
The result is 2021-05-02 00:00:00.
DAY
Retrieves the numerical day component from the specified date.
| Syntax | Input | Output |
|---|---|---|
| DAY(date) | Date in ISO format (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) as string, variable, or function. | Numeric value (integer). The values range from 1 through 31. |
Examples:
Function: DAY("2021-05-029:10:12")
The result is 2.
Function: DAY(NOW())
The result will be the day component of the current date and time.
MONTH
Retrieves the numerical month component from the specified date.
| Syntax | Input | Output |
|---|---|---|
| MONTH(date) | date: Date in ISO format (YYYY-MM-DD or YYYY-MM- DD hh:mm:ss) as string or variable. | Numeric value (integer). The values range from 1(January) through 12(December). |
Examples:
Function: MONTH("2021-05-02 9:10:12")
The result is 5.
Function: DAY(NOW())
The result will be the month component of the current date and time.
YEAR
Retrieves the year component from the specified date.
| Syntax | Input | Output |
|---|---|---|
| YEAR(date) | Date in ISO format (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) as string, variable, or function. | Numeric value (integer) |
Examples:
Function: YEAR("2021-05-02 9:10:12")
The result is 2021.
Function: YEAR(NOW())
The result will be the year component of the current date and time.
WEEKDAY
Returns the numerical day of the week for the specified date. The day range is 1 (Sunday) through 7 (Saturday).
| Syntax | Input | Output |
|---|---|---|
| WEEKDAY(date) | date: Date in ISO format (YYYY-MM-DD or YYYY-MM- DD hh:mm:ss) as string or variable. | Numeric value (integer) |
Example:
Function: WEEKDAY("2021-05-02 9:10:12")
The result is 1.
TEXT
Retrieves the specific date components in a date in string format.
| Syntax | Input | Output |
|---|---|---|
| TEXT\(date, format\_text\) | - date: Date in ISO format \(YYYY-MM-DD or YYYY-MM-DD hh:mm:ss\) as string, variable. - format\_text: Date components as string or variable that are to be extracted. | String |
Example:
TEXT("2022-08-17 9:10:12","yyyy-MM")
The result is 2022-08.
DATEVALUE
Converts a date in text format into a date in ISO format.
| Syntax | Input | Output |
|---|---|---|
| DATEVALUE\(date\_text\) | date\_text: Date stored as text must be in YYYY-MM-DD format. | Date in ISO format \(YYYY-MM-DD hh:mm:ss\) as string. |
Example:
Function: DATEVALUE("2021-05-02")
The result is 2021-05-02 00:00:00.
WORKDAY
Returns the nearest working day for the specified input date by excluding the specified holidays and weekends before or after the specified n number of days.
| Syntax | Input | Output |
|---|---|---|
| WORKDAY\(start\_date, days, holiday 1,holiday 2, ..., holiday n\) | - start\_date: Date in ISO format \(YYYY-MM-DD or YYYY-MM-DD hh:mm:ss\) as string or variable. - days: Number of days as a numeric value, string, or function. - holiday 1...holiday n \(Optional\): Date in ISO format \(YYYY-MM-DD or YYYY-MM-DD hh:mm:ss\) as string or variable. | Date in ISO format \(YYYY-MM-DD hh:mm:ss\) as string. |
Example:
Function: WORKDAY("2022-08-17 9:10:12",2)
The result is 2022-08-19 00:00:00.
NETWORKDAYS
Calculates the number of working days between two dates by excluding weekends and specified holiday dates. Number of working days includes the start date and end date.
| Syntax | Input | Output |
|---|---|---|
| NETWORKDAYS\(start\_date,end\_date,holiday 1,holiday 2, ... holiday n\) | - start\_date: Date in ISO format \(YYYY-MM-DD or YYYY-MM-DD hh:mm:ss\) as string or variable. - end\_date: Date in ISO format \(YYYY-MM-DD or YYYY-MM-DD hh:mm:ss\) as string or variable. - holiday 1,holiday 2, ... holiday n \(optional\) : List of holidays that should be excluded while calculating working days. | Numeric value \(integer\) |
Example:
Function: NETWORKDAYS("2022-08-17 20:10:12","2022-08-19 9: 10:12")
The result is 3.
Logical functions
Use logical functions to perform logical operations on column values.
AND
Performs a logical AND operation on the arguments.
| Syntax | Input | Output |
|---|---|---|
| AND(argument 1, argument 2) | String, function call, or variable | True or false |
Examples:
Function: AND(2>3, 4<5)
The result is false.
Formula: AND(LENGTH(sys_created_by)>25, LENGTH(sys_updated_by)>25)
The result is true only when the number of characters in both the sys_created_by and sys_updated_by columns are greater than 25. Otherwise, the result is false.
IF
Executes the specified statements based on the Boolean output of the conditional expression.
| Syntax | Input | Output |
|---|---|---|
| IF\(<conditional\_expression>, <do\_this\_when\_true>, <do this\_when\_false>\) |
Note: Logical comparison of strings is not supported in the conditional expression.
| String, numeric value, function call, or variable based on the Boolean output of the conditional expression. |
Examples:
Function: IF(number_of_incidents >= 5, "High", "Medium")
If the number of incidents is greater than 5, the string ‘High’ is returned. In other cases, the string ‘Medium’ is returned.
Function: IF(LENGTH(full_name) > 100, "Number of characters exceed the limit", "Number of characters within the limit")
If the number of characters for the full_name column is above 100, the string 'Number of characters exceed the limit' is returned. Otherwise, the string 'Number of characters within the limit' is returned.
OR
Performs logical OR operation on the arguments.
| Syntax | Input | Output |
|---|---|---|
| OR(argument 1, argument 2) | Conditional expression, function call, or variable | True or false |
Examples:
Function: OR(2>3,4<5)
The result is true.
Formula: OR(LENGTH(first_name)>25, LENGTH(last_name)<25)
The result is true when the number of characters in the first_name column is greater than 25 or the number characters in the last_name column is less than 25. Otherwise, the result is false.
IFERROR
Evaluates expression 1 and returns the expression 1 value when there are no errors in the expression 1. When an error occurs while evaluating expression 1, expression 2 is evaluated and expression 2 value is returned.
| Syntax | Input | Output |
|---|---|---|
| IFERROR\(expression 1, expression 2\) | - expression 1: Arithmetic, Logical expression, function call, String, numeric value or variable. - expression 2: Arithmetic, Logical expression, function call, String, numeric value or variable. | Result of expression 1 when there are no errors in expression 1. Otherwise, result of expression 2. |
Example:
Function: IFERROR( MULTIPLY(snr_factor, signal), MULTIPLY( default_factor, signal))
If the snr_factor value is a valid number, the multiplied value of snr_factor with signal is returned. If the snr_factor value is not a valid number, the multiplied value of default_factor value with signal is returned.