Formula Transformations
Formulas let you combine multiple operations into a single transformation. Instead of creating separate steps for each calculation, write one formula that does it all.Syntax
- Column names — Use the column name directly:
Amount,Fee,Rate - Column names with spaces or special characters — Wrap in brackets:
{Settled Amount}or[Settled Amount] - Numbers — Use directly:
100,2.5,0 - Text — Wrap in double quotes:
"-","USD" - Nesting — Any argument can be another formula:
ROUND(SUM(Amount, Fee), 2)
Available Functions
Math
| Function | Description | Example |
|---|---|---|
SUM | Add values together | SUM(Amount, Fee) |
SUBTRACT | Subtract values | SUBTRACT(Amount, Fee) |
MULTIPLY | Multiply values | MULTIPLY(Amount, Rate) |
DIVIDE | Divide (safe — returns empty if dividing by zero) | DIVIDE(Amount, 100) |
AVERAGE | Average of values | AVERAGE(Amount, Fee, Tax) |
ABS | Absolute value | ABS(Amount) |
ROUND | Round to N decimal places | ROUND(Amount, 2) |
Text
| Function | Description | Example |
|---|---|---|
LEFT | First N characters | LEFT(Reference, 3) |
RIGHT | Last N characters | RIGHT(Reference, 4) |
TRIM | Remove leading/trailing spaces | TRIM(Name) |
UPPER | Convert to uppercase | UPPER(Currency) |
LOWER | Convert to lowercase | LOWER(Status) |
CONCAT | Join values together | CONCAT(FirstName, " ", LastName) |
Logic
| Function | Description | Example |
|---|---|---|
IF | Conditional: IF(condition, then, else) | IF(Amount > 0, Amount, 0) |
>, <, >=, <=, =, !=
Arithmetic Operators
You can also use standard arithmetic directly:Examples
Calculate a percentage
Sum two columns and round
Build a composite key
Conditional value
Net amount after fee and tax
Percentage with 4 decimal places
Column Name Rules
| Column Name | How to Reference |
|---|---|
Amount | Amount |
Settled Amount | {Settled Amount} or [Settled Amount] |
Difference (fee dr) | {Difference (fee dr)} or [Difference (fee dr)] |
If the column name contains spaces, parentheses, or special characters, wrap it in{ }or[ ].
Limits
- Maximum formula length: 1,000 characters
- Maximum nesting depth: 10 levels
- Maximum function calls per formula: 20