Numeric Operators
Numeric operators enable you to generate new values based on a computation (e.g. 3 + 4
).
For each expression, the numeric operator is applied from left to right:
(left-hand side) (operator) (right-hand side)
These evaluations result in a numeric output, which can be an Integer or Decimal depending on the input values. The following operators are supported:
Operator Name | Symbol | Example Expression | Output | Notes |
---|---|---|---|---|
add | + |
| 9 | |
subtract | - |
| 1 | |
multiply | * | | 18 | |
divide | / |
| 5 | |
modulo | % | 5 % 4 | 1 | Computes the remainder as an integer of the first parameter divided by the second parameter. Input parameters must be Integers, column references to Integers, or an expression that evaluates to an Integer. |
power | pow | pow(4,3) | 64 | Power is implemented as a function. see POW Function. |
negate | - | -myColumn | opposite of the value in | This operator supports only one operand. Parenthetical references are supported. See the example below. |
The above examples apply to integer values only. Below, you can review how the comparison operators apply to different data types.
Usage
Numeric operators are used to perform numeric manipulations on columns of data, Integer or Decimal constants, or both. Typically, they are applied in evaluations of values or rows.
Example data:
X | Y |
---|---|
2 | 1 |
6 | 4 |
7 | 10 |
10 | 0 |
Transformations:
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | (X + Y) |
Parameter: New column name | 'add' |
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | (X - Y) |
Parameter: New column name | 'subtract' |
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | (X * Y) |
Parameter: New column name | 'multiply' |
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | (X / Y) |
Parameter: New column name | 'divide' |
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | (X % Y) |
Parameter: New column name | 'modulo' |
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | pow(X,Y) |
Parameter: New column name | 'power' |
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | -(X-Y) |
Parameter: New column name | 'negativeXminusY' |
Results:
Your output looks like the following. Below, (null value)
indicates that a null value is generated for the computation.
X | Y | add | subtract | multiply | divide | modulo | power | negativeXminusY |
---|---|---|---|---|---|---|---|---|
2 | 1 | 3 | 1 | 2 | 2 | 0 | 2 | -1 |
6 | 4 | 10 | 2 | 24 | 1.5 | 2 | 1296 | -3 |
7 | 10 | 17 | -3 | 70 | 0.7 | 7 | 282475249 | 3 |
10 | 0 | 20 | 10 | 0 | (null value) | (null value) | 1 | -10 |
Examples
Dica
For additional examples, see Common Tasks.
Nota
When a numeric operator is applied to a set of values, the type of data of each source value is re-inferred to match any literal values used on the other side of the expression. This method allows for more powerful comparisons.
In the following examples, values taken from the MySource
column are re-typed to match the inferred data type of the other side of the expression.
add
Column Type | Example Transformation | Output | Notes | ||||||
---|---|---|---|---|---|---|---|---|---|
Integer/Decimal |
| Generated values are sum of values in | |||||||
Datetime | You cannot directly add Datetime values. You can use the | ||||||||
String | You cannot add strings together.
| For computational purposes, all data types not previously listed in this table behave like strings. |
subtact
Column Type | Example Transformation | Output | Notes | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Integer/Decimal |
| Generated values are difference between values in | |||||||||||||
Datetime | You cannot directly subtract Datetime values. You must use the | ||||||||||||||
String | You cannot differentiate strings directly.
| For computational purposes, all data types not previously listed in this table behave like strings. |
multiply
Column Type | Example Transformation | Output | Notes | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Integer/Decimal |
| Generated values are the product of values in the | |||||||||||||
Datetime | N/A | ||||||||||||||
String | N/A |
divide
Column Type | Example Transformation | Output | Notes | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Integer/Decimal |
| Generated values are the values in the | |||||||||||||
Datetime | N/A | ||||||||||||||
String | N/A |
modulo
Column Type | Example Transformation | Output | Notes | ||||||
---|---|---|---|---|---|---|---|---|---|
Integer |
| Generated values are the values in the | |||||||
Decimal |
| Not supported. Inputs must be of Integer type. | |||||||
Datetime | N/A | ||||||||
String | N/A |