Skip to main content

Symon.AI help center

Formula

Abstract

Use the Formula tool to apply a formula to an existing column. The tool will create a new column with the transformed data.

Add a new column by applying formula(s) to an existing column. The tool creates a new column with the transformed data.

With the Output column(s) option, you can add new columns or replace existing selected columns in your data set. The columns append to your data set, using the formula name as the column name. This option is available in both the standard pipe and the big pipe.

For a list of all formula definitions with examples, see: Formula functions, operators and constants. You can also click the Info icon info_icon.png in the to-do sidebar to see a list of all formula definitions with examples.

When to use this tool

Use the Formula tool to apply a formula to an existing column. The tool will create a new column with the transformed data.

Input

The Formula tool requires one data input.

Configuration

Use the following configuration options to help create your Formula configuration.

Configuring the Formula tool
  1. In Pipe builder, add your data source.

  2. Click symon_add_icon.png +Add Tool.

  3. Click See all tools.

  4. In the search bar, search for Formula. Click Add tool.

    Tip

    You can also find the Formula tool in the Calculate section.

  5. Connect the tool to your data set.

  6. In the configuration pane, enter the following information:

    Table 35. Formula tool configuration

    Field

    Description

    Formula

    In the field, start typing your formula, or click the one of the options to add a function, constant or operator.

    Formula_field.png

    For more information about different formula functions, see: Formula functions, operators and constants.

    For information about data types you can use to construct formulas, see: Data types.

    Advanced

    Click to access the Advanced section of the configuration.

    Output column

    Select one of the following options: Adds a new column or Replace a selected column.

    + New formula

    Click to add another formula to the configuration.



Data types

Symon.AI uses the following data types to construct formulas:

  • Boolean: Used to indicate the result value of either True or False.

  • Double quote "": Used to to create strings or text, such as “This is a string” or “Male”.

  • Numeric: Used to indicate the value is in numeric form, either float or integer, such as 1 or 3.12.

  • Single quote '': Used to indicate columns, such as ‘Age’ refers to the column Age.

Limitations

The behavior of round() for floats can be surprising because most decimal fractions cannot be represented exactly as a float.

For example, round(2.675, 2) will return 2.67 instead of the expected 2.68.

You can read Floating Point Arithmetic: Issues and Limitations for more information.

Using Row Viewer Format

When you use the Row Viewer format, the format inherits the format from the preceding tool. But, some formulas may have a different output, based on one of the following scenarios:

  • There is not a preceding tool to inherit the format from.

  • The column types between the two tools are different and cannot inherit the format.

  • The function applied in the current tool takes precedence over the inherited format.

For more information, see Row Viewer

Some formula functions affect the decimal place without the Row Viewer format. The following functions always take precedence and prevent the inherited decimal place formatting:

  • round()

  • floor()

  • ceil()

The following functions use the inherited format:

  • abs()

  • sqrt()

  • log10()

  • sin()

  • cos()

  • asin()

  • acos()

  • atan()

  • pow()

if() outputs the same format if the true results and the false results have the same format. If they do not have the same format, the input formats are ignored.

Error messaging

The Formula tool can find the following errors in your data set:

  • Syntax error: An unknown function is occurring, or the function cannot parse due to a special character, or a missing bracket.

  • Invalid argument error: The tool detects that the argument is invalid due to a wrong data type.

  • Too many arguments error: The tool detects that there are too many arguments.

  • Missing arguments error: The tool detects that there are one or more arguments missing from the range of the number of allowed arguments.

  • Unsupported data type error: The tool detects an unsupported data type. The Formula tool only accepts number, boolean, string, and date data types.

Formula functions, operators and constants

Abstract

Combine formulas to transform data into an existing column. Symon.AI creates a new column with the output.

Combine formulas to transform data into an existing column. Symon.AI creates a new column with the output.

Functions

Symon.AI offers the following types of functions to use in your formulas:

Table 36. Formula functions

Function

Description

Example

Syntax

Notes

Abs

Returns the absolute value of a number.

If you want to show the difference between this month's sales and last month's sales as a percentage, but you do not want the percent change to be a negative value, use a formula similar to the following example:

ABS(Current - Prior)/Prior

In this example, the formula is subtracting last month's sales from this month's sales and dividing it by last month's sales to get the percentage difference.

Abs(Number)

  • Number: The number of which you want the absolute value.

Acos

Returns the arccosine of a number.

Acos.png

Acos(Number)

  • Number: The cosine of the acosine angle you want, must be from -1 to 1.

Asin

Returns the arcsine of a number.

Asin.png

Asin(Number)

  • Number: The sine of the asine angle you want, must be from -1 to 1.

Atan

Returns the arctangent of a number.

Atan.png

Atan(Number)

  • Number: The tangent of the angle you want.

Ceil

Rounds a number up to the nearest integer.

Ceil.png

Ceil(Number)

  • Number: The number or column that you want to round.

Concat

Concatenates all the given text and numbers.

concat_example.png

Concat(Texts/Numbers...)

  • Texts/Numbers...: Any number of columns (Text or Number), user input text or numbers to concatenate with each other.

Contains

Returns either true or false if Text contains the specified Target text.

contains_example.png

Contains(Text,Contained text)

  • Text: The text containing the text you want to find. This can be a column or user input text.

  • Contained text: The text you want to find. This can be a column or user input text.

Cos

Returns the cosine of a given angle.

cosine.png

Cos(Number)

  • Number: The angle in radians for which you want the cosine.

Date

Returns a date given the specified Year, Month, and Day.

Date_example.png

Date(Year,Month,Day)

  • Year: A four digit number representing the year or column.

  • Month: A one or two digit number representing the month or column.

  • Day: A one or two digit number representing the day of the month or column.

Endswith

Returns either true or false if Text ends with the specified Ending text.

endswith.png

Endswith(Text,Ending text)

  • Text: The text containing the text you want to find. This can be a column or user input text.

  • Ending text: The text you want to find. This can be a column or user input text.

Floor

Rounds a number down to the nearest integer.

floor.png

Floor(Number)

  • Number: The number or column that you want to round.

If

Returns the first input if true and the second input if false.

If the sale amount is greater than $100, you receive a 5% commission. Otherwise, you receive a 2% commission.

IF(Data.Value > 100, Data.Value * 0.05, Data.Value * 0.02)

Nested IF() example:

If your sale amount is greater than $100, you receive 5% commission. If your sale is greater than $50, you receive 3%; otherwise, you receive 2%:

IF(Data.Value > 100, Data.Value * 0.05, IF(Data.Value > 50, Data.Value * 0.03, Data.Value * 0.02))

If(Condition,True Result,False Result)

  • Condition: The condition you want to test.

  • True Result: The value to return if condition is true.

  • False Result: The value to return if condition is false.

Isnull

Returns true for empty rows. Returns false for records that are not empty.

isnull.png

Isnull(Column)

In general, any base operation between a real number and null results in null, such as the following examples:

  • 1+null = null1

  • *null = null

  • Column: The column to check for empty state.

Log10

Returns the base- 10 logarithm of a number.

Log10.png

Log10(Number)

  • Number: The positive real number for which you want the base- 10 logarithm.

Log2

Returns the base-2 logarithm of a number.

log2.png

Log2(Number)

  • Number: The positive real number for which you want the base-2 logarithm.

Max

Returns the maximum value between columns for numbers and dates.

If, for each deal, you receive a 5% commission, but there is a minimum of $5 per deal you earn, you can use the following formula to determine whether the 5% commission is higher than the $5 minimum:

MAX(Data.Value*0.05, $5)

Max(Numbers/Dates...)

  • Numbers/Dates...: Any columns (Number or Date), or user input numbers or dates of which you want to find the maximum.

  • If you have an If statement similar to the following example: IF(A>B,A,B), you may want to use the MAX operator.

Min

Returns the minimum value between columns for numbers and dates.

If 5% commission is paid on each deal to a maximum of $100, the following formula pays either 5% commission or $100, whichever is less:

MIN(Data.Value*0.05 , 100)

MIN(Data.Value)

Min.png

Min(Numbers/Dates...)

  • Numbers/Dates...: Any columns (Number or Date), or user input numbers or dates of which you want to find the minimum.

  • If you have an If statement similar to the following example: IF(A>B,A,B), you may want to use the M operator.

Mround

Returns a number rounded to the nearest multiple.

Mround.png

Mround(Number,Multiple)

  • Number: The value to round, either a numeric value or column.

  • Multiple: The multiple used to round the number, either a numeric value or column.

Now

Returns the current time.

Now_example.png

Now()

Pad

Adds leading or trailing characters to the text to meet the specified length.

Pad_example.png

Pad(Text,Text length,Pad text,Pad side)

  • Text: Column from data or user input.

  • Text length: Number of characters or column that indicates the text length for each row.

  • Pad text: User input or column, that contains single/multiple characters or whitespace. If left empty, it defaults to white space.

  • Pad side: "left" or "right" or a column that indicates the pad direction for each record.

Pow

Returns the result of a number raised to a power.

pow.png

Pow(Number,Power)

  • Number: The base number.

  • Power: The exponent to which the base number is raised.

Round

Rounds a number to a specified number of digits.

If you want to round your results to the nearest cent, use a formula similar to this example:

ROUND(Result, 2)

In this example, the formula rounds the output to 2.

Round(Number,Number of digits)

  • Number: The number or column that you want to round.

  • Number of digits: The number of digits to which you want to round to.

Sin

Returns the sine of a given angle.

sine.png

Sin(Number)

  • Number: The angle in radians for which you want the sine.

Sqrt

Returns a square root of a number.

Sqrt.png

Sqrt(Number)

  • Number: The number for which you want the square root.

Startswith

Returns either true or false if Text starts with the specified Starting text.

startswith.png

Startswith(Text,Starting text)

  • Text: The text containing the text you want to find. This can be a column or user input text.

  • Starting text: The text you want to find. This can be a column or user input text.

Tan

Returns the tangent of a given angle.

tan.png

Tan(Number)

  • Number: The angle in radians for which you want the tangent.



Operators

Symon.AI offers the following types of calculation operators to use in your formulas:

Table 37. Operators

Operator

Description

Example

Add +

Returns result of the addition of specified numbers.

formula('col1')+formula('col2').

For example: Ceil('CreditValue')+Ceil('Commission')

and

Filters data based on the specified conditions. Use to combine multiple conditions.

and(value1, value2)

Divide /

Returns the result of the division of specified numbers.

formula('col1')/formula('col2')

For example: Abs('Commission')/Abs('ManualPay')

Equal ==

Returns a true result if the number is equal to another number. If the number is not equal, it returns a false result.

formula('col1')==formula('col2')

For example: Abs('Commission')==Abs('ManualPay')

Less than <

Returns a true result if the left number is less than the right number. If the right number is greater, it will return a false value.

formula('col1')<formula('col2')

For example: Abs('Commission')<Abs('ManualPay')

Greater than >

Returns the result of the number is greater than another number.

formula('col1')>(formula('col2').

For example: Ceil('CreditValue')>Ceil('Commission')

Greater than or equal >=

Returns the result of the number is greater than or equal to another number.

formula('col1')>=(formula('col2').

For example: Ceil('CreditValue')>=Ceil('Commission')

Less than or equal <=

Returns the result of the number less than or equal to another number.

number1<=number2

For example: 30<=45

Multiply *

Returns the result of the multiplication of specificied numbers.

formula('col1')*formula('col2')

For example: Ceil('Rate')*Ceil('CreditValue')

Module %

Returns the remaining value after the number is divided by a divisor.

formula('col1')%formula('col2').

For example: Ceil('CreditValue')&Ceil('Commission')

not

Returns the value when one condition is not true.

not(value1, value2)

Not equal !=

Returns a true result if the number is not equal to another number. If the number has the same value, it returns a false result.

formula('col1')!=formula('col2')

For example: Abs('Commission')!=Abs('ManualPay')

or

Filters data based on the specified conditions. Use to combine multiple conditions. Need at least one condition to be true or both conditions true.

or(value1, [value2])

Subtract -

Returns the result of the the subtraction of specified numbers.

formula('col1')-formula('col2')

For example: Ceil('CreditValue')-Ceil('Commission')

Text ""

A text value within a formula.

"value"

xor

Filters data based on the specified conditions. Use to combine multiple conditions. Need at least one condition to be false or both conditions false.

xor(value1, [value2])



Constants

Symon.AI offers the following types of constant operators to use in your formulas:

Table 38. Constants

Constant

Description

Example

Natural base e

Returns the natural logarithm of a value to the base of the mathematical constant e.

=e

phi

Returns the sum of the value of two numbers based on the phi mathematical constant value.

=phi

pi

Returns the sum of the value based on the pi mathematical constant value.

=pi

tau

Returns the sum of the value of two numbers based on the tau mathematical constant value.

=tau



Data types

Symon.AI uses the following data types to construct formulas:

  • Boolean: Used to indicate the result value of either True or False.

  • Double quote "": Used to to create strings or text, such as “This is a string” or “Male”.

  • Numeric: Used to indicate the value is in numeric form, either float or integer, such as 1 or 3.12.

  • Single quote '': Used to indicate columns, such as ‘Age’ refers to the column Age.

Limitations

The behavior of round() for floats can be surprising because most decimal fractions cannot be represented exactly as a float.

For example, round(2.675, 2) will return 2.67 instead of the expected 2.68.

You can read Floating Point Arithmetic: Issues and Limitations for more information.

Using Row Viewer Format

When you use the Row Viewer format, the format inherits the format from the preceding tool. But, some formulas may have a different output, based on one of the following scenarios:

  • There is not a preceding tool to inherit the format from.

  • The column types between the two tools are different and cannot inherit the format.

  • The function applied in the current tool takes precedence over the inherited format.

For more information, see Row Viewer

Some formula functions affect the decimal place without the Row Viewer format. The following functions always take precedence and prevent the inherited decimal place formatting:

  • round()

  • floor()

  • ceil()

The following functions use the inherited format:

  • abs()

  • sqrt()

  • log10()

  • sin()

  • cos()

  • asin()

  • acos()

  • atan()

  • pow()

if() outputs the same format if the true results and the false results have the same format. If they do not have the same format, the input formats are ignored.

Error messaging

The Formula tool can find the following errors in your data set:

  • Syntax error: An unknown function is occurring, or the function cannot parse due to a special character, or a missing bracket.

  • Invalid argument error: The tool detects that the argument is invalid due to a wrong data type.

  • Too many arguments error: The tool detects that there are too many arguments.

  • Missing arguments error: The tool detects that there are one or more arguments missing from the range of the number of allowed arguments.

  • Unsupported data type error: The tool detects an unsupported data type. The Formula tool only accepts number, boolean, string, and date data types.