Calculator
The Calculator Component adds new columns by performing calculations. Each input row produces one output row. Any pre-existing columns that share a name with a created column will be overwritten.
By default, all input columns are mapped into output columns of the same name. You can then use the expression editor to add new columns with a user-defined calculation.
It is possible to include Matillion ETL variables when writing expressions in the Calculator component using the syntax ${<VariableName>}. Additionally, if you click Manage Variables, you can manage both Environment Variables and Job Variables.
Properties
Name = string
A human-readable name for the component.
Include Input Column = drop-down
When Yes, all input columns are mapped without modification. When No, the component will output only columns that have been mapped manually via the expression editor. The default setting is Yes.
Calculations = expression editor
- List of Expression: A list of expressions calculated per row of data. The name of the expression becomes the output column. Each expression must be valid SQL and use the built-in data platform functions. Expressions can refer to an earlier expression by enclosing the existing expression name in double quotes. Data types of columns may be expressed as icons, as explained in Variables.
- Use Grid Variable: Select this to switch editor. From here, you can select a Matillion ETL grid variable and assign a calculation column and an output column.
Strategy
Generates a select query where the calculated expressions are in-line after the select statement.
Functions
Calculator expressions must be valid SQL and use the data platform's built-in functions. For function documentation, refer to the following.
| Data platform | Functions docs |
|---|---|
| Snowflake | Snowflake functions |
| Delta Lake on Databricks | AWS built-in functions (Databricks SQL) or Azure Functions, Built-in functions (Databricks SQL, AWS only) |
| Amazon Redshift | Redshift functions |
| Google BigQuery | BigQuery functions, operators, and conditionals |
| Azure Synapse Analytics | Azure SQL database functions (applies to Snowflake, Synapse, Delta Lake on Databricks) |
| Snowflake | Delta Lake on Databricks | Amazon Redshift | Google BigQuery | Azure Synapse Analytics |
|---|---|---|---|---|
| ✅ | ✅ | ✅ | ✅ | ✅ |