Expression transformations are used for row-wise manipulation. For any type of manipulation you wish to perform on an individual record, use an Expression transformation. The Expression transformation accepts the row-wise data, manipulates it, and passes it to the target. The transformation receives the data from the input port and sends the data out from output ports.
Use Expression transformations for any row-wise calculation, such as if you want to concatenate the names, get the total salary, and convert it to uppercase. To understand the functionality of the Expression transformation, let's take a scenario.
Using flat file as the source, which we we created in Chapter 1, Starting the Development Phase – Using the Designer Screen Basics, concatenate FIRST_NAME
and LAST_NAME
to get FULL_NAME
and TOTAL_SALARY
from JAN_SALARY
and FEB_SALARY
of an 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 of transformations, such as ports in transformations, writing a function, and so on while we implement our first transformation using an expression.
Perform the following steps to achieve the functionality:
EMP_FILE
as the source and TGT_EMP_FILE
as the target.m_EXP_CONCAT_TOTAL
mapping in Mapping Designer, drag the source and target from the navigator to the workspace, and create the Expression transformation with the EXP_CONCAT_TOTAL
name.We have connected EMPLOYEE_ID
, AGE
, and LOCATION
directly to the target as no manipulation is required for these columns.
At this step, we need to understand how to use the different types of ports in the transformation.