Formula
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 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.
In Pipe builder, add your data source.
Click +Add Tool.
Click See all tools.
In the search bar, search for Formula. Click Add tool.
Tip
You can also find the Formula tool in the Calculate section.
Connect the tool to your data set.
In the configuration pane, enter the following information:
Table 35. Formula tool configurationField
Description
Formula
In the field, start typing your formula, or click the one of the options to add a function, constant or operator.
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
orFalse
.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 acceptsnumber
,boolean
,string
, anddate
data types.
Formula functions, operators and constants
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:
Function | Description | Example | Syntax | Notes |
---|---|---|---|---|
| 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:
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. |
|
|
| Returns the arccosine of a number. |
|
| |
| Returns the arcsine of a number. |
|
| |
| Returns the arctangent of a number. |
|
| |
| Rounds a number up to the nearest integer. |
|
| |
| Concatenates all the given text and numbers. |
|
| |
| Returns either true or false if Text contains the specified Target text. |
|
| |
| Returns the cosine of a given angle. |
|
| |
| Returns a date given the specified Year, Month, and Day. |
|
| |
| Returns either true or false if Text ends with the specified Ending text. |
|
| |
| Rounds a number down to the nearest integer. |
|
| |
| 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.
Nested 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%:
|
|
|
| Returns true for empty rows. Returns false for records that are not empty. |
In general, any base operation between a real number and null results in null, such as the following examples:
|
| |
| Returns the base- 10 logarithm of a number. |
|
| |
| Returns the base-2 logarithm of a number. |
|
| |
| 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:
|
|
|
| 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:
|
|
|
| Returns a number rounded to the nearest multiple. |
|
| |
| Returns the current time. |
| ||
| Adds leading or trailing characters to the text to meet the specified length. |
|
| |
| Returns the result of a number raised to a power. |
|
| |
| 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:
In this example, the formula rounds the output to 2. |
|
|
| Returns the sine of a given angle. |
|
| |
| Returns a square root of a number. |
|
| |
| Returns either true or false if Text starts with the specified Starting text. |
|
| |
| Returns the tangent of a given angle. |
|
|
Operators
Symon.AI offers the following types of calculation operators to use in your formulas:
Operator | Description | Example |
---|---|---|
Add | Returns result of the addition of specified numbers. |
For example: |
| Filters data based on the specified conditions. Use to combine multiple conditions. |
|
Divide | Returns the result of the division of specified numbers. |
For example: |
Equal | Returns a |
For example: |
Less than | Returns a |
For example: |
Greater than | Returns the result of the number is greater than another number. |
For example: |
Greater than or equal | Returns the result of the number is greater than or equal to another number. |
For example: |
Less than or equal | Returns the result of the number less than or equal to another number. |
For example: |
Multiply | Returns the result of the multiplication of specificied numbers. |
For example: |
Module | Returns the remaining value after the number is divided by a divisor. |
For example: |
| Returns the value when one condition is not true. |
|
Not equal | Returns a |
For example: |
| Filters data based on the specified conditions. Use to combine multiple conditions. Need at least one condition to be |
|
Subtract | Returns the result of the the subtraction of specified numbers. |
For example: |
Text | A text value within a formula. |
|
| Filters data based on the specified conditions. Use to combine multiple conditions. Need at least one condition to be |
|
Constants
Symon.AI offers the following types of constant operators to use in your formulas:
Constant | Description | Example |
---|---|---|
Natural base | Returns the natural logarithm of a value to the base of the mathematical constant |
|
| Returns the sum of the value of two numbers based on the |
|
| Returns the sum of the value based on the |
|
| Returns the sum of the value of two numbers based on the |
|
Data types
Symon.AI uses the following data types to construct formulas:
Boolean: Used to indicate the result value of either
True
orFalse
.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 acceptsnumber
,boolean
,string
, anddate
data types.