.png){kind=logo}
Calculator
-
DarkLight
Calculator
-
DarkLight
Calculator Component
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
Snowflake Properties | ||
|---|---|---|
| Property | Setting | Description |
| Name | Text | A human-readable name for the component. |
| Include Input Column | Yes/No | 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 | 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 Snowflake Functions. Expressions can refer to an earlier expression by enclosing the existing expression name in double quotes. Note: Data types of columns may be expressed as icons, as explained in Variables. |
| Use Grid Variable | Check Use Grid Variable to switch editor. From here, you can select a Matillion ETL grid variable and assign a calculation column and an output column. | |
Redshift Properties | ||
|---|---|---|
| Property | Setting | Description |
| Name | Text | A human-readable name for the component. |
| Include Input Column | Yes/No | 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 | 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 Redshift Functions. Expressions can refer to an earlier expression by enclosing the existing expression name in double quotes. Note: Data types of columns may be expressed as icons, as explained in Variables. |
| Use Grid Variable | Check Use Grid Variable to switch editor. From here, you can select a Matillion ETL grid variable and assign a calculation column and an output column. | |
BigQuery Properties | ||
|---|---|---|
| Property | Setting | Description |
| Name | Text | A human-readable name for the component. |
| Include Input Column | Yes/No | 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 | 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 BigQuery Functions. Expressions can refer to an earlier expression by enclosing the existing expression name in double quotes. Note: Data types of columns may be expressed as icons, as explained in Variables. |
| Use Grid Variable | Check Use Grid Variable to switch editor. From here, you can select a Matillion ETL grid variable and assign a calculation column and an output column. | |
Synapse Properties | ||
|---|---|---|
| Property | Setting | Description |
| Name | Text | A human-readable name for the component. |
| Include Input Column | Yes/No | 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 | 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 Azure Functions. Note: Data types of columns may be expressed as icons, as explained in Variables. |
| Use Grid Variable | Check Use Grid Variable to switch editor. From here, you can select a Matillion ETL grid variable and assign a calculation column and an output column. | |
Delta Lake Properties | ||
|---|---|---|
| Property | Setting | Description |
| Name | String | A human-readable name for the component. |
| Include Input Column | Yes/No | 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 | 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 AWS Built-in functions (Databricks SQL) or Azure Functions. Expressions can refer to an earlier expression by enclosing the existing expression name in double quotes. Note: Data types of columns may be expressed as icons, as explained in Variables. |
| Use Grid Variable | Check Use Grid Variable 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
- Snowflake: Function Reference.
- Redshift: SQL functions reference.
- BigQuery: Functions, operators, and conditionals.
- Azure: What are the SQL database functions? (applies to Snowflake, Synapse, Delta Lake on Databricks).
- Built-in functions (Databricks SQL) (AWS only).