Tutorial corner

Informatica,ETL,oracle,sql/plsql

How to use expression transformation in informatica

Spread the love

In the last article , we learn about the Expression transformation in Informatica , steps to create it , configuring the port. We also discussed the various properties of Expression Transformation. Here we will discuss How to use expression transformation in informatica mapping with example

How to use expression transformation in informatica

How to use expression transformation in informatica

How to use expression transformation in informatica

Create a mapping to populate Target  table with  employee name and job name concatenated. It should also populated the total annual Salary considering the commission as well.

Check here for filter transformation example in informatica

Source Data:

EMPNO ENAME JOB SAL COMM

1

JOHNSON ADMIN

18000

2

HARDING MANAGER

52000

300

3

TAFT SALES I

25000

500

4

HOOVER SALES I

27000

5

LINCOLN TECH

22500

1400

Target Data:

EMPNO ENAME_WITH_JOB JOB SAL COMM TOTAL_SALERY

1

JOHNSON(ADMIN) ADMIN

18000

216000

2

HARDING(MANAGER) MANAGER

52000

300

624300

3

TAFT(SALES I) SALES I

25000

500

300500

4

HOOVER(SALES I) SALES I

27000

324000

5

LINCOLN(TECH) TECH

22500

1400

271400

 

 Solution:

  • Create a new mapping m_expression_example by Go to toolbar -> click mapping-> Create
  • Drag Source (EMP) and Target  (EMP_TAR) to the mapping.
  • Add Expression Transformation by  “Go to Toolbar->click Transformation -> Create. Select the Expression transformation”
    Filter Transformation Part 1

    Expression Transformation Step 1

    Expression Transformation Steps

    Expression Transformation Step 2

  • Give a name Enter a name like exp_Salery_exp, click on Create and then click on Done.
  • Drag all required port (EMPNO , ENAME ,JOB,MGR,HIREDATE,SAL,COMM)  from Source Qualifier to filter transformation
  • Double click exp_Salery_exp Expression to edit it
  • Create two output port (ENAME_WITH_JOB ,TOTAL_SALARY) and one variable v_annual_Salery.
  • Expression Transformation

    Expression Transformation Step 3

  • Click on expression button for the ENAME_WITH_JOB and add expression to concatenate ENAME and JOB as ENAME||'(‘||JOB||’)’ . You can use Concatenate operator from Operator Keypad  as show in below figure.
  • Expression Transformation in informatica

    Expression Transformation Step 5

  • Click on validate button to validate the expression
  • Similarly edit the expression for v_annual_sal  =  SAL * 12

    Expression Transformation in informatica

    Expression Transformation in informatica

  • Similarly edit the Total_Salery port with expression as v_annual_salery+ IIF(ISNULL(COMM),0,COMM). Here in case of NULL commission we are adding 0.
  • Now join the output port of exp_salery_exp to EMP_TAR.
  • Click on Mapping ( from tool bar) -> then Validate ( to validate the mapping)
  • Now save the mapping  ( by clicking on Repository-> Save or you can also press Ctrl+S)
  • Generate the workflow and run it

Check here for filter transformation

Over all Mapping:

Expression Transformation in informatica

Expression Transformation in informatica

So , in this article we have gone through example of Expression Transformation in Informatica mapping,  learnt step to add it , configure the output /variable port . Hope you enjoyed the article. Please feel free to give your feedback.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme