Expression transformation is a connected, passive transformation used to calculate values on a single row. Examples of calculations are concatenating the first and last name, adjusting the employee salaries, converting strings to date etc. Expression transformation can also be used to test conditional statements before passing the data to other transformations.
Creating an Expression Transformation
Just follow the below steps to create an expression transformation
1.In the mapping designer, create a new mapping or open an existing mapping.
2.Go to Toolbar->click Transformation -> Create. Select the expression transformation.
3.Enter a name, click on Create and then click on Done.
4.You can add ports to expression transformation either by selecting and dragging ports from other transformations or by opening the expression transformation and create ports manually.
Adding Expressions
Once you created an expression transformation, you can add the expressions either in a variable port or output port. Create a variable or output port in the expression transformation. Open the Expression Editor in the expression section of the variable or output port. Enter an expression and then click on Validate to verify the expression syntax. Now Click OK.
Expression Transformation Components or Tabs
The expression transformation has the following tabs
Transformation: You can enter the name and description of the transformation. You can also make the expression transformation reusable.
Ports: Create new ports and configuring the ports.
Properties: Configure the tracing level to set the amount of transaction detail to be logged in session log file.
Metadata Extensions: You can specify extension name, data type, precision, value and can also create reusable metadata extensions.
Configuring Ports:
You can configure the following components on the ports tab
Port name: Enter a name for the port.
Datatype: Select the data type
Precision and scale: set the precision and scale for each port.
Port type: A port can be input, input/output, output or variable.
Expression: Enter the expressions in the expression editor.
Expression transformation examples
1.Create a mapping to increase the salary of an employee by 10 percent?
Solution:
In the expression transformation, create a new output port (call it as adj_sal) and enter the expression as salary+salary*(10/100)
The expression can be simplified as salary*(110/100)
2.Create a mapping to concatenate the first and last names of the employee? Include space between the names
Solution:
Just create a new port in the expression transformation and enter the expression as CONCAT(CONCAT(first_name,' '),last_name)
The above expression can be simplified as first_name||' '||last_name
