Transformations
Formula
Runs several calculations per row in one node: each line is Name = formula, and later lines can use what earlier lines worked out.
When to use it
Use it instead of a chain of Calculated Column nodes when values build on each other — gross, then tax, then net — or when you need a working value that should not end up as a column.
In a pipeline
File InputFormulaExport Excel
Settings
| Setting | Type | Required | Default or hint |
|---|---|---|---|
| Statements (one per line or ;) | textarea | Yes | var Gross = {Qty} * {Price} Tax = ROUND({Gross} * 0.2, 2) Net = {Gross} + {Tax} |
Example
Gross, tax and net in one step
- Statements (one per line or ;)=var Gross = {Qty} * {Price}; Tax = ROUND({Gross} * 0.2, 2); Net = {Gross} + {Tax}
| Item | Qty | Price |
|---|---|---|
| Widget | 2 | 12.5 |
| Bolt | 10 | 0.35 |
| Item | Qty | Price | Tax | Net |
|---|---|---|---|---|
| Widget | 2 | 12.5 | 5 | 30 |
| Bolt | 10 | 0.35 | 0.7 | 4.2 |
Gross is a var: later statements use it as {Gross}, but it is not written as a column.
Worth knowing
- Columns are written {Column Name}; variables as $Name. The functions and operators are the same as Calculated Column's.
- A new name becomes a column at the end of the table; the name of an existing column overwrites it in place, e.g. Price = {Price} * 1.1.
- Separate statements with new lines or semicolons. A semicolon inside quotes or {braces} is part of the statement.
- A row that cannot be calculated gets an empty value; the node reports which statement failed and why.