Calculator
The Calculator transformation component lets you perform calculations on your data, and add the results of these calculations as new columns. By default, all existing columns are included in the output as well. However, if you use this component to create a new column with the same name as an existing column, the existing data will be overwritten.
This component is equivalent to writing a SELECT query, where the calculated expressions are in-line after the SELECT statement.
Use case
This component is extremely versatile and can be used to perform a wide range of calculations on your data. For example, you can use it to:
- Perform mathematical operations, such as calculating year-over-year growth.
- Perform string manipulations, such as converting email addresses to lower case.
- Perform operations with conditions, such as assigning values to customers based on their total spend.
Properties
Name = string
A human-readable name for the component.
Include Input Columns = boolean
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
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 can use all of the built-in functions supported by your cloud data warehouse:
Expressions can refer to an earlier expression by enclosing the existing expression name in double quotes.
To use warehouse functions or variables in your expression, type the name of the function or variable prefixed by the dollar symbol and surrounded by { } brackets, as follows: ${variable}. Once you type ${, a drop-down list of autocompleted suggested functions and variables will appear. This list updates as you type; for example, if you type ${DAY, functions and variables containing DAY will be listed. This list also shows the type of each function or variable.