Use of Case Transform
Case transform is basically like ‘select… case’ in VB or ‘switch… case’ in C programming language.
This is also similar to the “Router Transform” available in Informatica ETL tool. Whenever there are multiple filter conditions or we want to route data
to multiple pipe lines based on some conditions, we use case transform.
In this Scenario we will transfer data from employee table into flat file based on the Department of the employee.
For example employees with ‘SAP’ as a department will be transferred into one flat file while employees with ‘JAVA’ as a department will be in the another file.
1) Create Job, workflow & dataflow as usual.
2) Add Employee table to the data flow. It contains data as follows.
3) Add Case Transform to the data flow.
4) Add Conditions for case transform.
- Double click on Case Transform, click on “Add ” add label as shown below.
- Add condition as shown below for the label “DEPT_SAP”.
- Similarly add another label & condition as shown below.
As we have to split the data based on the department, we have created the conditions.
5) Create a file format for the target system.
- Double click on the table dragged in the data flow.
- Right click on the table name in the tree & click on ” Create File Format”.
- File Format window will open, add necessary detail as shown below.
One can use already created file format also.
6) Drag file format to dataflow , join “Case Transform” to file format & specify the condition.
Here we are specifying the “DEPT_SAP” condition as we want put the details of SAP employees in this table.
Now it will look like this.
Make copy of the FIle Format used in the data flow & change the name of the output file.
Join Case transform to second file format & specify another condition as follows.
7) Save the job & validate it.
8) Execute the job & see the output of the 2 file format.