Expression Transformation

The Expression Transformation is an Passive and Connected Transformation.

The Expression transformation to calculate values in a single row before it writes into Target.

For example, you might need to adjust employee salaries, concatenate first and last names, or convert strings to numbers. You can also use the Expression transformation to test conditional statements before you pass the results to a target or other transformations.

The Expression transformation is use to perform non-aggregate calculations.

Task can be performed using Expression Transformation:

The transformation language provides the following types of functions:

•  Aggregate - Only Row level Addition, Subtraction, Multiplication and Division
• Character
• Conversion
• Data Cleansing
• Date
• Encoding
• Financial
• Numerical
• Scientific
• Special
• String
• Test
• Variable

Generally we use Expression transformation for Test, Character and Conversion function like IF...ELSE..THEN,  DECODE, ISNULL, IS_DATE, IS_SPACES, IS_NUMBER, etc..

The Expression Transformation having 3 types of port:

• Input: The Input port is that which received data directly from its before transformation.
• Output: The Output port is that which return value of the expression or a Hard coded Value
• Variable: The Variable port is that Temporary value of a current row or a Hard coded value.

The Variable port and Output port are used to perform a expression for each record passing through it. We can have multiple expressions in single expression transformation. We can have 'N' no of Variable and Output Port in single expression transformation.

Example: Create a mapping to sum salary and commission for each employee. (Row level calculation)

Sample Mapping:

1. Connect and Open the folder if not already opened.

2. Select Tools --> Mapping Designer

3. Select Mappings --> Create

It will pop-up "Mapping Name". Enter the mapping name of your choice" and Click on 'OK'. Example: m_emp_salary

4. Drag the Source and Target definitions into workspace if they are already exist. If not click here to know how to create or import Tabledefinitions.

5. Select 'Transformation' from Menu --> Create

a)    That will appear you 'Select the transformation type to create:'

 b)    Select 'Expression' from drop down and 'Enter a new name for this transformation:' as "exp_employee_salary"
c)    Click 'Create' and 'Done'

or

  
a)    Click on Expression Transformation icon marked below in below snapshot.

 b)    Click in the workspace in Mapping Designer.

 c)    Select EXPTRANS in workspace and Right Click --> Edit.

d)    In Transformation tab --> Click on 'Rename' highlighted above which will pop-up 'Rename Transformation'. Enter the Transformation Name: "exp_employee_salary"

e)    Click on 'OK'

f)    Click on 'Apply' and 'OK'.

6). Drag required ports from 'SQ_EMP' Source Qualifier to 'exp_employee_salary' Expression Transformation.

7) Select 'exp_employee_salary' Expression Transformation and Right Click --> Edit --> Ports Tab.

a) Un-Check Output Ports for SAL and COMM

Before: See the port highlighted in red.

After: See the ports highlighted in red.

b) Add the Variable port to expression transformation.

Note: Use the below 'Icon' to handle the port as commented.

·         Add a new port to the transformation and name it 'v_Salary' and check 'Variable check box' as shown above

·         Click on 'Open Browser' and Write a Expression in "Formula:" box like 'SAL+COMM' or First_Name||Last_Name.

·         Click on 'Validate' to validate function and Click on 'OK'

c) Add Output port to expression transformation.

• Add a new port to the transformation and name it 'o_Salary' and check 'Output check box' as shown above
• Click on 'Open Browser' and Write a Expression in "Formula:" box like 'v_Salary' which is variable value
• Click on 'Validate' to validate function and Click on 'OK'

d) Click on 'Apply' and Click on 'OK'.

8). Drag required port from 'exp_employee_salary' expression to 'EMP_DEPT_FILE' Target as below:

 

9) Select 'Mapping' from Menu --> Validate.

10) Select 'Repository' from Menu --> Save.

Note:

You make the transformation reusable; you cannot copy ports from the source qualifier or other transformations. You can create ports manually within the transformation.