Expression Transformation in Informatica

Expression transformation is a connected, passive transformation used to calculate values on a single row.
Expression transformations are used for row-wise manipulation.
For any 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.

An expression transformation is created with following types of ports.

1. Input port (I)
2. Output Port (O)
3. Variable Port (V)

• Data Manipulation      : concatenation( CONCAT or || ) , Case change (UPPER,LOWER) truncation, InitCap (INITCAP)
• Datatype conversion :  (TO_DECIMAL, TO_CHAR, TO_DATE)
• Data cleansing – check nulls (ISNULL) , replace chars, test for spaces (REPLACESTR) , test for number
• Manipulate dates – convert, add, test (GET_DATE_PART, IS_DATE, DIFF_DATES)
• Scientific calculations and numerical operations – exponential, power, log, modulus (LOG, POWER, SQRT)
• ETL specific – if, lookup, decode (IIF, DECODE).

Open folder where we want to create the mapping.

Click Tools -> Mapping Designer.

Click Mapping -> Create -> Give mapping name. Ex: m_totalsal

Drag EMP from source in mapping.

Click Transformation -> Create -> Select Expression from list. Give name and click Create. Now click done.

Now create a new port out_Total_SAL. Make it as output port only.
Click the small button that appears in the Expression section of the dialog box and enter the expressi

on in the Expression Editor.
Enter expression SAL + 10000 . You can select SAL and 10000 from Ports tab in expression editor.
Check the expression syntax by clicking Validate.
Click OK -> Click Apply -> Click Ok.
Now connect the ports from Expression to target table.
Click Mapping -> Validate
Repository -> Save

What is Filter Transformation?

Filter transformation is an active, connected transformation.
The filter transformation is used to filter out rows in a mapping.
It may change the number of rows passed through it.
You have to specify a filter condition in the filter transformation.
The rows that meet the specified filter condition are passed to other transformations.
The rows that do not meet the filter condition are dropped.


 For example, for loading the employee records having deptno equal to 20 only, we can put filter transformation in the mapping with the filter condition deptno=20.

Step 1 – Create a mapping having source "EMP" and target "EMP_TAR"

source "EMP"

 target "EMP_TAR"

  

 mapping having source "EMP" and target "EMP_TAR"

Step 2 – Then in the mapping

1. Select Transformation menu
2. Select create option Filter Transformation from the list

Step 3 – The filter transformation will be created, Select "Done" button in the create transformation window.

Step 4 – In the mapping

1. Drag and drop  the Source qualifier columns to the filter transformation.

Step 5 – Double click on the filter transformation to open its properties, and then

1. Select the properties menu.
2. Click on the Filter condition editor.
3. Then in the filter condition expression editor.
4. Enter filter condition – deptno=20
5. Select OK button.

Step 6 – Now again in the edit transformation window in Properties tab you will see the filter condition, select OK button.

Step 7 – Link the columns from filter transformation to the target table.

Step 8 – Save the mapping.
1). create new workflow

Step 9 –   Create the connection ,link tasks and session.

Step 10 –   Save the workflow.

1) Right click and start workflow.
2) check the workflow monitor.

Step 10 –   Connect target database and check the emp_tar table.