Expression transformation

Expression transformation is used for row-wise manipulation. For any type of manipulation you wish to do on an individual record, use expression transformation. Expression transformation accepts row-wise data, manipulates it, and passes to the target. The transformation receives the data from the input port and sends the data out from the output ports.

Use the expression transformation for any row-wise calculation, for instance, if you want to concatenate the names, calculate the total salary and convert in upper case, and so on. To understand functionality of expression transformation, let's consider the following scenario:

Scenario for expression transformation: Using the flat file we created in Chapter 3, Understanding Designer Screen and its Components as the source, concatenate FIRST_NAME and LAST_NAME to get FULL_NAME, and get TOTAL_SALARY from JAN_SALARY and FEB_SALARY of individual employee.

We are using expression transformation in this scenario because the value of FULL_NAME can be achieved by concatenating FIRST_NAME and LAST_NAME of an individual record. Similarly, we can get TOTAL_SALARY using JAN_SALARY and FEB_SALARY. In other words, the manipulation required is row wise.

We are going to learn some basic aspects about transformations such as ports of transformations, writing a function, and so on while we implement our first transformation using Expression.

Perform the following steps to achieve the functionality:

1. Create the source using the flat file in Source Analyzer and target in Target Designer. We will be using EMP_FILE as the source and TGT_EMP_FILE as the target.
2. Create a new mapping m_EXP_CONCAT_TOTAL in mapping designer, drag the source and target from Navigator to Workspace, and create the Expression Transformation with the name EXP_CONCAT_TOTAL.
3. Drag all the columns from source qualifier to expression transformation. At this point, the mapping will look like this:
   

We have connected EMPLOYEE_ID, AGE and LOCATION directly to target as there is no manipulation needed for those columns.

At this step, we need to understand how to use the different types of ports in the transformation.