Pages

Sunday, March 17, 2013

Suppose that a source contains a column which holds the salary information prefixed with the currency code , for example


EMPNO ENAME      JOB              MGR HIREDATE         SAL            DEPTNO
7369 SMITH            CLERK           7902 17-DEC-80        $300               20             
7499 ALLEN      SALESMAN        7698 20-FEB-81         £1600               30             
7521 WARD       SALESMAN        7698 22-FEB-81        ¥8500                30     
In the target different currency will evaluate to a single currency value, for  example covert all to Rupees.
  1. First thing we should consider that there are different types of currency like pound, dollar, yen etc.So it’s a good idea to use mapping parameter or variable.Go to mapping=> mapping parameter and variables then create three parameters (for this example) and set its initial value as bellow
    mapping parameter
  2. Then drag the source to mapping area and connect to an expression transformation.
    source to target mapping
  3. In expression create a output port as sal1 and make sal as input only as bellow.
    Expression Transformation port
  4. In sal1 port write the condition as below
    iif(instr(SAL,'$')!=0,TO_integer(SUBSTR(SAL,INSTR(SAL,'$')+1,LENGTH(SAL)-1))*$$DOLAR,
    iif(instr(SAL,'£')!=0,TO_integer(SUBSTR(SAL,INSTR(SAL,'£')+1,LENGTH(SAL)-1))*$$POUND,
    iif(instr(SAL,'¥')!=0,TO_integer(SUBSTR(SAL,INSTR(SAL,'¥')+1,LENGTH(SAL)-1))*$$YEN
    )
    )
    )Expression condition editor
    $$DOLAR,$$POUND,$$YEN these are mapping parameter . you can multiply price in rupee directly  for example dollar price in rupees i.e 48 .
  5. Connect required output port from expression to target directly. And run the session.

No comments:

Post a Comment