Calculated columns
Calculated Column formulas can be used to perform a wide range of operations on data, from simple arithmetic to complex logical and statistical computations. Whether you're rounding numbers, extracting portions of dates, calculating averages, or transforming texts, these formulas provide the versatility to generate the data you need.
Here are some typical use cases for Calculated Column functions:
Data Cleaning and Transformation: Effortlessly clean and reformat data, such as trimming whitespace, changing text casing, or converting date formats.
Statistical Analysis: Perform statistical calculations like averages, variances, or standard deviations to derive insights from numerical data sets.
Logical Operations: Apply logical tests to data to create flags, indicators, or to filter and categorize data based on specific criteria.
Mathematical Operations: Execute a variety of mathematical operations, enabling complex calculations like financial modelling or engineering calculations.
Text and Date Manipulation: Extract or transform portions of text and date fields, which is particularly useful in data preparation for reporting or further analysis.
Note that when a calculated column is applied to generated data, it is processed last. This ensures that all generated data and applied mockers are considered before the calculation occurs, preserving the intended transformations and logic.
Syntax
Calculated Columns are composed of a library of functions and operators used to build formulas and expressions in Syntho. When creating calculated columns in Syntho, you must follow specific syntax rules and best practices. Here are the fundamental syntax rules for Calculated Column expressions when creating calculated columns:
Formula Structure
Formulas are expressions made from:
Functions like
ROUND(...)orDATEADD(...)Column references like
[AMOUNT]Constants like
2,"US", orDATE(2024, 12, 31)Operators like
+,-,*,/,=,<>Mockers like
MOCK_FIRST_NAMEorMOCK_FREE_EMAIL_DOMAIN
Column references
When referring to a column within the same table, you can just use the column name in square brackets: [ColumnName]. Only references to columns within the same tables are supported.
Functions
Calculated Column includes a wide range of functions that can be used in your expressions, such as time intelligence functions (DATE, DATEADD), logical functions (IF, AND, OR), and many more. Functions follow the syntax of FUNCTION_NAME(arguments).
Mock generators
If you would like to add a mocker to a calculated column formula, type "MOCK_" and you will see the names of mockers in the dropdown list. Either choose from the list or add the name of the mocker to the end of "MOCK_", with whitespaces replaced by underscores. Therefore, mockers like "First name female" and "Company Email" should be entered as "MOCK_FIRST_NAME_FEMALE" and "MOCK_COMPANY_EMAIL," respectively. A complete list of mockers can be found in the Mockers section.
When you add a mock generator inside a Calculated Column formula, the settings for that mock generator can be configured in the right-hand screen.
Each mock generator you insert into a formula is automatically given a unique identifier by appending an underscore followed by a number (e.g., MOCK_FIRST_NAME_1). This identifier allows you to reuse the same mock generator value multiple times within the same formula by referring to it via the assigned identifier.
Operators
Calculated Column supports various operators for arithmetic calculations (+, -, *, /), and comparison (<, >, <=, >=, =, <>) operations.
Constants
You can use constants like numbers (123), strings ("text"), and dates (DATE(2020, 12, 31)).
Whitespace and Line Breaks
Whitespace and line breaks do not affect the interpretation of the formula but can make your formulas easier to read.
Comments
Certain sections in a formula can be commented out by encapsulating them with (* and *). These sections will be ignored when processing the formula. This can be useful when you want to add comments to a formula or when are creating larger complex formulas, for which you want to evaluate that certain sections result in the expected output. If you select a formula section and press CTRL + / the selected section will be commented out.
Examples
1) ROUND — round numbers to a fixed number of digits
Use ROUND when you need consistent decimal precision (for example, currency).
number(number | numeric column): value to round. Example:[AMOUNT].count(integer): number of digits to keep.2keeps two decimals.0rounds to whole numbers.-1rounds to tens,-2to hundreds, etc.
2) IF — return one value when a condition is true, else another
Use IF to map rows into labels based on a boolean test.
test(boolean expression): must evaluate toTRUEorFALSE. Example:[COUNTRY] = "US".then_value(any type): returned whentestisTRUE. Example:"domestic".otherwise_value(any type, optional): returned whentestisFALSE. Example:"international". If omitted,IFreturnsFALSE.
3) DATEADD — shift a date forward or backward
Use DATEADD to create derived dates like “due date” or “follow-up date”.
dates(date/datetime column): base date to shift. Example:[ORDER_DATE].number_of_intervals(integer): how many units to add or subtract. Example:14. Use negative values to subtract.interval(string): unit for the shift. Use"year","quarter","month", or"day".
4) Invoice total (combined) — tax + discount with null-safe inputs
Compute an invoice total that still works when tax rate or discount is missing.
IFNULL([TAX_RATE], 0)falls back to0when[TAX_RATE]isNULL.IFNULL([DISCOUNT], 0)falls back to0when[DISCOUNT]isNULL.ROUND(..., 2)rounds to 2 decimals for currency-like output.
5) Normalized email (combined) — conditional separators + normalization + generated domain
Build a stable email-like value from name columns, even with missing parts.
IFNULL(..., "")preventsNULLfrom nulling the entire result.IF(ISNULL([LASTNAME]), "", ".")only inserts.when a last name exists.TRIM(...)removes leading and trailing whitespace.LOWER(...)standardizes casing.SUBSTITUTE(..., " ", "")removes remaining spaces (for double names).
Examples use existing columns and mockers. Results depend on each input row.
Limitations & considerations
Column names that begin with an underscore (e.g.,
_MyColumn) will cause “Invalid formula” errors. Rename any columns that start with an underscore so that they begin with a letter or digit instead.You can only reference columns within the same table as the calculated column formula. Referencing columns in other tables is not supported.
Calculated Columns
Categorical, Discrete, Continuous, Date
Supported calculated column functions
ABS
Returns the absolute value of a number.
Syntax
Parameters
number
Any number, column or mock value.
Return value
An absolute value of the number.
Examples
Returns the absolute value of the [HEALTHCARE_COVERAGE] column for each row.
Varies by row (e.g., [HEALTHCARE_COVERAGE]=-20 → 20).
Returns the absolute value of a literal number.
5
Returns the absolute value of a generated random digit.
Varies by row (e.g., MOCK_RANDOM_DIGIT=7 → 7).
Returns the absolute difference between [HEALTHCARE_COVERAGE] and [HEALTHCARE_EXPENSES] for each row.
Varies by row (e.g., [HEALTHCARE_COVERAGE]=250 and [HEALTHCARE_EXPENSES]=100 → 150).
Flags rows where the absolute difference between coverage and expenses exceeds 100.
Either "outlier" or "ok" per row.
AND
The AND function returns TRUE if all provided arguments evaluate to TRUE, and FALSE if any argument evaluates to FALSE.
A common use case for the AND function is enhancing other functions that perform logical tests. For instance, the IF function tests a condition and returns one value if the condition is TRUE and another value if it is FALSE. By using AND as the logical_test argument in the IF function, you can test multiple conditions simultaneously rather than just one.
Syntax
Parameters
Logicalvalue1
The first condition to evaluate, which must result in either TRUE or FALSE.
Logicalvalue2
Additional conditions to evaluate, up to a maximum of 30, each of which must result in either TRUE or FALSE.
Return value
Returns TRUE if all arguments are TRUE.
Examples
Checks if gender is male and healthcare coverage exceeds 100.
TRUE or FALSE per row.
Checks three conditions at once: state, minimum coverage, and maximum expenses.
TRUE or FALSE per row.
Classifies rows as "adult" when age is between 18 and 64 inclusive.
Either "adult" or "senior_or_minor" per row.
Checks that a row is active and not marked as deleted.
TRUE or FALSE per row.
Flags US rows with a missing ZIP code.
Either "missing_us_zip" or "ok" per row.
BASE
Converts a number into its text representation in the specified radix (base).
Syntax
Parameters
number
The number, column or mock value to be converted. It must be an integer greater than or equal to 0 and less than 2^53.
radix
The base (radix) to convert the number into. It must be an integer between 2 and 36, inclusive.
MinimumLength
The minimum length of the returned string. It must be an integer greater than or equal to 0.
Return value
Converts a positive integer to a specified base into a text from the numbering system.
Examples
Converts the decimal number 5 to base 2 (binary).
101
Converts 5 to base 2 and left-pads with zeros to length 8.
00000101
Converts the decimal number 42 to base 16 (hexadecimal).
2A
Converts the [NUMBER] column to the base specified in [RADIX] for each row.
Varies by row.
Converts a random digit using a random radix. Radix must be between 2 and 36.
Varies by row (or errors if radix is out of range).
Converts [NUMBER] to base 36 and pads the output to length 6.
Varies by row.
Builds an account-like identifier by converting [ACCOUNT_ID] to padded hex and prefixing with acct_.
Varies by row.
CEILING
Rounds a number up, away from zero, to the nearest multiple of a specified significance. The function always rounds a value up, away from zero, regardless of the sign of the number. If the number is an exact multiple of the significance, no rounding is performed. If both the number and significance are negative, the value is rounded down, away from zero. If the number is negative and the significance is positive, the value is rounded up toward zero.
Syntax
Parameters
number
The column, number or mock value to be rounded.
significance
The multiple to which you want to round the number.
Return value
A number rounded up to the nearest multiple of Significance.
Examples
Rounds 3.1 up to the nearest multiple of 1.
4
Rounds -4.5 to the nearest multiple of -2, away from zero.
-6
Rounds -2.5 to the nearest multiple of 2, toward zero.
-2
Rounds 5.5 up to the nearest multiple of 0.1.
5.5
Rounds 0.9345 up to the nearest multiple of 0.001.
0.935
Rounds the absolute amount up to the nearest 0.05.
Varies by row.
Rounds amounts to whole numbers while keeping the original sign.
Varies by row.
CLEAN
Removes all nonprintable characters from a given text. The CLEAN function is useful for cleaning text imported from other applications that may contain characters not supported by your operating system. For example, it can remove low-level computer code that often appears at the beginning or end of data files and cannot be printed.
Syntax
Parameters
text
The text, column or mock values from which you want to remove nonprintable characters.
Return value
The text that has been "cleaned" of line breaks and other non-printable characters.
Examples
Removes non-printable characters from the string, leaving only readable text.
"Patient Medications"
Cleans non-printable characters around the [description] column value.
Varies by row.
Cleans non-printable characters around a generated first name.
Varies by row.
Trims whitespace and removes non-printable characters from [DESCRIPTION].
Varies by row.
Joins a generated first and last name and removes any non-printable characters.
Varies by row.
CONCATENATE
The CONCATENATE function joins two or more text strings into a single string.
Syntax
Parameters
text1
The first item to join. This can be a text, column or mock values.
text2, ...text30
Additional text items to join. You can include up to 30 items, with a total character limit of 8,192.
Return value
Several text strings combined into one string.
Examples
Joins state and county into a single string per row.
Varies by row (e.g., "Massachusetts, Bristol County").
Builds a full name using mock generators.
Varies by row.
Joins literal strings into a full name.
"Brittany Jenkins"
Uppercases the state and joins it with the county, separated by -.
Varies by row (e.g., "MASSACHUSETTS-Bristol County").
Builds a lowercase email-like string from name columns plus a generated domain.
Varies by row.
DATE
The DATE function creates a date value by combining individual year, month, and day components. This function is useful for combining separate year, month, and day values into a single date.
Syntax
Parameters
year
A number, column or mock value representing the year.
month
A number, column or mock value column representing the month of the year.
day
A number, column or mock value column representing the day of the month.
Return value
The specified date as the number of full days since nullDate.
Examples
The following examples build date values from literal values, columns, and other functions.
Creates the date for August 24, 2012.
2012-08-24
Creates the same date, but from string inputs.
2012-08-24
Creates a date per row from the [year], [month], and [day] columns.
Varies by row.
Creates January 1st of “current year - 18”.
Varies by run date.
Creates the first day of the current month, using [YEAR] per row.
Varies by row and run date.
DATEADD
Returns a column of dates, shifted forward or backward by the specified number of intervals.
Syntax
Parameters
dates
A column or mocker that contains dates.
number_of_intervals
A numeric value specifying the number of intervals to add or subtract from the dates. For calendar intervals (year, quarter, month), fractional values are truncated before applying the shift.
interval
The interval used to shift the dates. Options include: year, quarter, month, week, day, hour, minute, or second.
Return value
A column of date values.
Examples
Shifts [Birthdate] 20 years forward.
Varies by row.
Shifts [Birthdate] 6 months backward.
Varies by row.
Shifts [OrderDate] 14 days forward.
Varies by row.
Shifts [OrderDate] 12 hours forward.
Varies by row.
Parses a text date first, then shifts it 7 days forward.
Varies by row.
Shifts [BIRTHDATE] by 0 or 1 year depending on [IS_LEAP_YEAR].
Varies by row.
DATEVALUE
Description
The DATEVALUE function converts a date stored as text into a date. Note that the results of the DATEVALUE function may vary depending on your computer's system date settings. The DATEVALUE function is useful when you have dates in a text format that you want to filter, sort, format as dates, or use in date calculations.
Syntax
Parameters
date_text
A text column, string or mock representing a date.
Return value
A date that is converted from text to a date in datetime format.
Examples
Converts [BIRTHDATE] stored as text into a date per row.
Varies by row.
Converts a literal date string into a date value (system locale dependent).
A date value.
Converts a literal date string into a date value (system locale dependent).
A date value.
Converts a literal date string into a date value.
2024-06-24
Extracts the year after converting a text date column.
Varies by row.
Converts [START_DATE_TEXT] to a date, then shifts it 30 days forward.
Varies by row.
DAY
The DAY function returns the day of a date represented by a serial number, with the day provided as an integer ranging from 1 to 31.
Syntax
Parameters
number
The date, column or mock date for which you want to find the day. Dates should be entered using the DATE function or as results from other formulas or functions.
Return value
The day of the given date value.
Examples
Returns the day-of-month from a literal date string.
24
Returns the day-of-month for [BIRTHDATE] per row.
Varies by row.
Returns the day-of-month for a constructed date.
24
Returns the day-of-month after shifting [BIRTHDATE] 1 year forward.
Varies by row.
Flags whether [INVOICE_DATE] falls on the first day of a month.
Either "month_start" or "other_day" per row.
DAYS
The DAYS function calculates the number of days between two dates.
Syntax
Parameters
date1
The later of the two dates column between which you want to calculate the number of days.
date2
The earlier of the two dates column between which you want to calculate the number of days.
Return value
The difference between two date values.
Examples
Returns the number of days between two literal dates.
10
Returns days between two date columns per row.
Varies by row.
Returns the days between Jan 1 and Dec 31, 2024.
365
Returns the absolute number of days between two date columns.
Varies by row.
Classifies stays as short (≤ 30 days) or long.
Either "short_stay" or "long_stay" per row.
DATEDIFF
The DateDiff function returns the number of interval units between two dates.
Syntax
Parameters
date1, date2
An integer specifying the number of intervals to add or subtract from the dates.
interval
A string expression that specifies the time interval you want to use to calculate the difference between date1 and date2.
The value can be one of the following:
"second"
"minute"
"hour"
"day"
"week"
"month"
"quarter"
"year"
Return value
The number of units between two dates as defined in the interval.
Examples
Returns the number of days between [EXPIRY_DATE] and [INITIAL_DATE] per row.
Varies by row.
Returns the age in years, based on [BIRTHDATE].
Varies by row and run date.
Returns the number of months between Jan 1 and Feb 1, 2024.
1
Parses two text dates first, then returns the days between them.
Varies by row.
Classifies customers as "new" if they signed up in the last 30 days.
Either "new" or "existing" per row.
DELTA
The DELTA function checks whether two values are equal. It returns 1 if number1 equals number2, and 0 otherwise.
Syntax
Parameters
number_1
The first number, column or mocker.
number_2
The second number, column or mocker. If omitted, column2 is assumed to be zero.
Return value
Returns TRUE (1) if both numbers are equal, otherwise returns FALSE (0).
Examples
Returns 1 when [HEALTHCARE_COVERAGE] equals [HEALTHCARE_EXPENSES], else 0.
0 or 1 per row.
Checks equality of two literal numbers.
1
Checks whether [COPAY_AMOUNT] equals 0 (second argument defaults to 0).
0 or 1 per row.
Maps rows to "unknown" when [STATUS_CODE] equals 0.
Either "unknown" or "known" per row.
Checks equality after rounding [AMOUNT] and [AMOUNT_APPROVED] to whole numbers.
0 or 1 per row.
EVEN
The EVEN function rounds a number up to the nearest even integer. The function always rounds a value up and away from zero, regardless of its sign. If the number column is already an even integer, no rounding occurs.
Syntax
Parameters
number
The number, column or mocker to be rounded up to the nearest even integer.
Return value
A positive number rounded up to the next even integer and a negative number rounded down to the next even integer.
Examples
Rounds [HEALTHCARE_COVERAGE] up (away from zero) to the nearest even integer per row.
Varies by row.
Rounds 3.2 up to the next even integer.
4
Rounds -3.2 down (away from zero) to the next even integer.
-4
Rounds [HEALTHCARE_COVERAGE] to a whole number first, then to the nearest even integer.
Varies by row.
Makes odd [SEAT_NUMBER] values even by adding 1; leaves even values unchanged.
Varies by row.
FALSE
Returns the logical value FALSE.
Syntax
Return value
The logical value FALSE.
Examples
Returns the logical value FALSE.
FALSE
Returns TRUE when [IS_ACTIVE] equals 1; otherwise returns FALSE.
TRUE or FALSE per row.
Demonstrates that a constant FALSE() condition always returns the otherwise value.
"always"
Shows that AND returns FALSE when any argument is FALSE.
FALSE
Always returns the otherwise branch, so it generates last names.
Varies by row.
FLOOR
The FLOOR function rounds a number down toward zero to the nearest multiple of a specified significance. When the number is positive, it is rounded down and adjusted toward zero. When the number is negative, it is rounded down and adjusted away from zero. If the number is already an exact multiple of the significance, no rounding occurs.
If either argument is nonnumeric, the function returns an error message.
If the
numberis positive and thesignificanceis negative, the function returns an error message.
Syntax
Parameters
number
The numeric number, column or mocker you want to round down.
significance
The multiple to which you want to round the number.
Return value
Rounds a number down to the nearest multiple of Significance.
Examples
The following formulas round numbers down to the nearest multiple of Significance.
Rounds 7.7 down to the nearest multiple of 3.
6
Rounds -3.4 down to the nearest multiple of -3.
-3
Rounds 1.453 down to the nearest multiple of 0.01.
1.45
Rounds [AMOUNT] down to the nearest whole number per row.
Varies by row.
Rounds the absolute amount down to the nearest multiple of 0.5.
Varies by row.
HEX2DEC
The HEX2DEC function converts a hexadecimal number to its decimal equivalent. If the input is not a valid hexadecimal number, the function returns an error.
Syntax
Parameters
number
The hexadecimal number, column or mocker column to convert. The number can have up to 10 characters (40 bits). The most significant bit represents the sign, and the remaining 39 bits represent the magnitude. Negative numbers are represented using two's-complement notation.
Return value
The decimal number for the hexadecimal number entered.
Examples
Converts a literal hex string to its decimal value.
13283335
Converts "FF" (hex) to decimal.
255
Converts the hex value in [HEX_VALUE] to decimal per row.
Varies by row.
Uppercases [HEX_VALUE] first, then converts to decimal.
Varies by row.
Extracts the last 6 hex characters from [HEX_PAYLOAD], then converts to decimal.
Varies by row.
HOUR
The HOUR function returns the hour component of a time value as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). Time values are part of a date value and are represented as decimal numbers (e.g., 12:00 PM is represented as 0.5 because it is half of a day).
Syntax
Parameters
time
The time number, column or mocker from which you want to extract the hour. This can be entered as a text string within quotation marks (e.g., "6:45 PM"), as a decimal number (e.g., 0.78125 representing 6:45 PM), or as the result of other formulas or functions (e.g., TIMEVALUE("6:45 PM")).
Return value
Hour component of given time.
Examples
Extracts the hour from a literal datetime string.
7
Extracts the hour from the [START] datetime column per row.
Varies by row.
Extracts the hour from a literal time string.
18
Classifies rows based on whether [START] occurs at 09:00 or later.
Either "business_hours" or "off_hours" per row.
Adds one day to [START] and extracts the hour (same hour, shifted date).
Varies by row.
IF
The IF function performs a logical test and returns one value if the test evaluates to TRUE and another value if it evaluates to FALSE. This function is commonly used to create conditional formulas.
Syntax
Parameters
test
The condition you want to test. This can be any expression that returns either TRUE or FALSE.
then value
The column or mock value that is returned if the logical_test evaluates to TRUE.
otherwisevalue
The column or mock value that is returned if the logical_test evaluates to FALSE. If omitted, the function returns FALSE by default.
Return value
A logical test result.
Examples
Generates a female first name for MA rows with coverage over 30; otherwise generates a male first name.
Varies by row.
Returns [HEALTHCARE_COVERAGE] when it’s positive; otherwise returns FALSE (default otherwise).
Either a number or FALSE per row.
Maps the sentinel ZIP value "0000XX" to "unknown", otherwise "known".
Either "unknown" or "known" per row.
Classifies rows as "adult" when age is 18+; otherwise "minor".
Either "adult" or "minor" per row.
Builds a full name, optionally inserting [MIDDLE_NAME] when present.
Varies by row.
IFS
The IFS function checks multiple conditions and returns a value corresponding to the first condition that evaluates to TRUE. This function simplifies the use of multiple nested IF statements, making it easier to read and manage multiple conditions.
To specify a default result, enter TRUE as the final condition argument. If none of the other conditions are met, the corresponding value will be returned. For example, if all other conditions fail, a default value can be returned by using TRUE as the final condition. If a condition argument is provided without a corresponding value, the function will return an error message. If a condition argument evaluates to something other than TRUE or FALSE, the function returns an error. If none of the conditions evaluate to TRUE, the function returns an error.
Syntax
Parameters
condition1
The condition that is evaluated to determine if it is TRUE or FALSE.
number1
The column or mock value returned if condition1 evaluates to TRUE. This can be left empty.
condition2…condition_N
Additional conditions that are evaluated to determine if they are TRUE or FALSE.
number2 …number_N
Th column or mock value returned if the corresponding conditionN evaluates to TRUE. Each valueN is associated with its corresponding conditionN. These can also be left empty.
Return value
A value that corresponds to the first true condition.
Examples
Generates a mock female name for "F" and a mock male name for "M".
Varies by row (or errors if no condition matches).
Maps states to abbreviations, with "OTHER" as a default.
"MA", "CA", or "OTHER" per row.
Assigns a grade based on score thresholds.
"A", "B", "C", or "D" per row.
Classifies US/non-US rows and whether a US row has a state value.
"US_WITH_STATE", "US_MISSING_STATE", or "NON_US" per row.
Validates phone numbers by trimmed length, with a default "valid".
"invalid" or "valid" per row.
IFNULL
Returns replacement_value when value is NULL; otherwise returns value.
Syntax
Parameters
value
The value, column or generator you want to check for NULL.
replacement
The value, column or generator to replace a NULL value with.
Return value
Returns the specified replacement value if the observed value is NULL.
Examples
Replaces missing ZIP codes with "0000XX".
[ZIP] or "0000XX" per row.
Replaces a missing middle name with an empty string.
[MIDDLE_NAME] or "" per row.
Replaces a missing generated postcode value with a fallback.
Varies by row.
Trims [CITY], then replaces a missing value with "Unknown".
[CITY] (trimmed) or "Unknown" per row.
Uses ISNULL and IFNULL together to guarantee a ZIP fallback.
[ZIP] or "0000XX" per row.
ISNULL
The ISNULL function checks whether a value is NULL and returns TRUE if the cell is NULL. If the value contains any data, including a formula that returns an empty string (""), ISNULL returns FALSE.
Syntax
Parameters
value
The value, column or mocker you want to check for NULL.
Return value
TRUE if the value is NULL; FALSE if it contains any data.
Examples
Checks whether [ZIP] is NULL.
TRUE or FALSE per row.
Checks whether [MIDDLE_NAME] is NULL.
TRUE or FALSE per row.
Maps missing ZIP codes to "missing", otherwise "present".
Either "missing" or "present" per row.
Returns TRUE when [EMAIL] is present (not NULL).
TRUE or FALSE per row.
Checks whether both phone fields are NULL.
TRUE or FALSE per row.
ISODD
The ISODD function checks whether a number is odd. It returns 1 if the number is odd, and 0 if the number is even. This function is useful for determining the parity of a number within a table. If the number is nonnumeric, ISODD returns an error.
Syntax
Parameters
value
The value or column to test. If the number is not an integer, it is truncated.
Return value
0 if the value is an even integer; 1 if the value is odd.
Examples
Checks whether [HEALTHCARE_EXPENSES] is odd.
0 or 1 per row.
Checks whether the literal number 7 is odd.
1
Adds 1 to the expenses value, then checks if the result is odd.
0 or 1 per row.
Labels invoice ids by parity.
Either "odd_id" or "even_id" per row.
Converts minutes to whole hours and checks if the hour count is odd.
0 or 1 per row.
LEFT
The LEFT function returns a specified number of characters from the left side of a string. To determine the number of characters in a string, use the LEN function.
Syntax
Parameters
"text"
The string, column or mocker from which the leftmost characters are returned. If the string contains Null, the function returns Null.
number
A numeric expression or column indicating how many characters to return. If 0, a zero-length string ("") is returned. If the length is greater than or equal to the number of characters in the string, the entire string is returned.
Return value
The input text capped by a specified character length.
Examples
Returns a random-length left substring from [STATE] per row.
Varies by row.
Returns the first 4 characters of a literal string.
"Mass"
Returns the first 0 characters, which is an empty string.
""
Trims [STATE] first, then returns the first 2 characters.
Varies by row (e.g., "Ma").
Builds "City, State" then returns the first 12 characters.
Varies by row.
LEN
The LEN function returns a Long value representing the number of characters in a string or the number of bytes required to store a variable.
Syntax
Parameters
"text"
Any valid string expression, column or mocker.
Return value
Length of a given text.
Examples
Returns the number of characters in [BIRTHPLACE] per row.
Varies by row.
Returns the number of characters in a literal string.
6
Builds "City, State" then returns the character count.
Varies by row.
Trims whitespace from [ADDRESS] then returns the character count.
Varies by row.
Validates phone numbers based on their length.
Either "invalid" or "valid" per row.
LN
The LN function returns the natural logarithm of a specified number. Natural logarithms are based on the mathematical constant e (approximately 2.71828182845904). The LN function is the inverse of the EXP function.
Syntax
Parameters
number
The positive real number, column or mocker for which you want to calculate the natural logarithm.
Return value
The natural logarithm of the number.
Examples
Returns the natural log of [HEALTHCARE_EXPENSES] per row.
Varies by row (and errors for non-positive values).
Returns the natural log of a literal number.
~2.3026
Adds 1 to avoid LN(0) for zero values, then logs the result.
Varies by row.
Takes the absolute value first, then computes the natural log.
Varies by row (and errors when the absolute value is 0).
Computes LN only for positive values; otherwise returns 0.
Either 0 or a log value per row.
LOG
The LOG function returns the logarithm of a specified number to a base that you define.
Syntax
Parameters
number
The positive real number, column or mocker for which you want to calculate the logarithm.
base
The base of the logarithm. If omitted, the base is assumed to be 10.
Return value
The logarithm of a number to the specified base.
Examples
Returns the base-2 log of [HEALTHCARE_EXPENSES] per row.
Varies by row (and errors for non-positive values).
Returns the base-10 log of 100 (default base).
2
Returns the log using a per-row base from [LOG_BASE].
Varies by row.
Returns the base-10 log of [HEALTHCARE_EXPENSES] per row.
Varies by row.
Computes base-2 log and rounds it to 3 decimals.
Varies by row.
LOG10
The LOG10 function returns the base-10 logarithm of a specified number.
Syntax
Parameters
number
The positive real number, column or mocker for which you want to calculate the base-10 logarithm.
Return value
The base-10 logarithm of a number.
Examples
Returns the base-10 log of [HEALTHCARE_EXPENSES] per row.
Varies by row (and errors for non-positive values).
Returns the base-10 log of 1000.
3
Adds 1 first, then computes the base-10 log.
Varies by row.
Computes LOG10 only for positive values; otherwise returns 0.
Either 0 or a log value per row.
Computes base-10 log and rounds it to 2 decimals.
Varies by row.
LOWER
The LOWER function converts all uppercase letters in a text string to lowercase.
Syntax
Parameters
"text"
The text, column or mocker you want to convert to lowercase. The LOWER function does not alter characters in the text that are not letters.
Return value
Text converted to lowercase.
Examples
Converts [ADDRESS] to lowercase per row.
Varies by row.
Converts a literal string to lowercase.
"john.doe@example.com"
Builds "First.Last" then lowercases it.
Varies by row.
Trims whitespace from [EMAIL], then lowercases it.
Varies by row.
Builds a lowercase email-like string using name columns plus a generated domain.
Varies by row.
MINUTE
The MINUTE function returns the minute component of a time value as an integer, ranging from 0 to 59. Time values are a portion of a date value and are represented as a decimal number (e.g., 12:00 PM is represented as 0.5 since it is half of a day).
Syntax
Parameters
time
The time, column or mocker from which you want to extract the minute. This can be entered as a text string (e.g., "4:30 PM"), as a decimal number (e.g., 0.78125 representing 6:45 PM), or as the result of other formulas or functions.
Return value
Minute component of given time.
Examples
Extracts the minute component from a literal time string.
30
Extracts the minute component from the [START] time/datetime per row.
Varies by row.
Extracts the minute component from a literal datetime string.
5
Flags whether [START] happens exactly on the hour.
Either "on_the_hour" or "off_cycle" per row.
Converts a time to minutes since midnight.
Varies by row.
MOD
Calculates the remainder of a division and always ensures it matches the sign of the divisor for consistency.
If the divisor is 0,
MODreturns the#DIV/0!error value.The
MODfunction can be expressed using theINTfunction as follows: &#xNAN;MOD(n, d) = n - d*INT(n/d)
Syntax
Parameters
dividend
The number, column or mocker for which you want to find the remainder.
divisor
The number, column or mocker by which you want to divide the number.
Return value
The remainder when one integer is divided by another.
Examples
The following formula returns the remainders when integers with different signs is divided by integers with different signs.
Returns the remainder of 5 / 2.
1
Returns the remainder of -5 / 2 (same sign as the divisor).
1
Returns the remainder of 5 / -2 (same sign as the divisor).
-1
Returns the remainder of -5 / -2 (same sign as the divisor).
-1
Returns the last digit (mod 10) of [INVOICE_ID] per row.
Varies by row.
Classifies invoice ids as even or odd using a mod 2 check.
Either "even" or "odd" per row.
MONTH
The MONTH function returns the month of a date represented by a serial number. The month is provided as an integer ranging from 1 (January) to 12 (December).