Day5

Removing '$' symbol from salary column

Q21: Reading a source file with salary prefix $ , in the target the Sal column must store in number .
Source
EMPNO ENAME      JOB              MGR HIREDATE         SAL            DEPTNO
7369 SMITH            CLERK           7902 17-DEC-80        $800                20             
7499 ALLEN      SALESMAN        7698 20-FEB-81         $1600               30             
Target
EMPNO ENAME      JOB              MGR HIREDATE         SAL            DEPTNO
7369 SMITH            CLERK           7902 17-DEC-80        800                20             
7499 ALLEN      SALESMAN        7698 20-FEB-81         1600               30     

1. Drag the source to mapping area and connect each port to an expression transformation.
2. In expression transformation add a new col sal1 and make it as out put and sal as in put  only as shown in picture.
3. In expression write the condition like this.
4.  connect the required port to target.

Currency convertor

Q22 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
2. Then drag the source to mapping area and connect to an expression transformation.
3. In expression create a output port as sal1 and make sal as input only as bellow.
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
)
)
)
$$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.

sending data one after another to three tables in cyclic order

Q23 In source  there are some record. Suppose I want to send three targets. First record will go to first target, Second one will go to second target and third record will go to third target and then 4th to 1st,5th to 2nd , 6th to  3rd and so on.

1. Put the source to mapping and connect it to an expression transformation.
2. Drag an sequence generator transformation and set properties like this And connect  the next value port to expression.
3. Drag all output port of expression to router. In router make three groups and gve the conditions Like this 
4. connect desire group to desire target .

Converting '$' symbol to 'RS." in sal column

Q24 The Emp table contains the salary and commission in USD, in the target the com and sal will converted to a given currency prefix ex: Rs.

 Source

EMPNO ENAME      JOB              MGR HIREDATE         SAL            DEPTNO 

7369 SMITH            CLERK           7902 17-DEC-80        $800                20              

7499 ALLEN      SALESMAN        7698 20-FEB-81         $1600               30              

Target

EMPNO ENAME      JOB              MGR HIREDATE         SAL                 DEPTNO 

7369 SMITH            CLERK           7902 17-DEC-80        Rs.800                20    

7499 ALLEN      SALESMAN        7698 20-FEB-81          RS.1600               30                        

1. Drag the source and connect it to expression transformation
2. In expression make a output port sal1 and make sal as input port only.
3.  In sal1 write the condition as like bellow
4.  Then send it to target.

Insert and reject records using update strategy

Scenario:There is a emp table and from that table insert  the data to targt where sal<3000 and reject other rows.

Following are the steps for achieving it

1. connect out-puts from SQF to Update Strategy transformation.
2. In properties of  Update Strategy write the condition like this
3. Connectthe Update Strategy to target

Count the no of vowel present in emp_name column

Scenario:Count the no of vowels present in emp_name column of EMP table as shown bellow.
     emp_name         total_vowels_count
       Allen                          2
       Scott                          1
       Ward                         1

These are the steps to achieve it

1. Connect required columns from SQF to an expression transformation.
2.  In Expression add 6 columns like in the picture as bellow. But You can make it two columns( One for all the vowels and one for the vowel counts). For better understanding I have added 6 columns,5 for each of the vowels and one for the vowel count.
   The way I achieved is for each of the vowels in ename , I replaced it with null and in port total vowel count , I  substract the vowel port from the ename length which gives me the individual count of vowels, after adding up for all vowels I found all the vowels present. Here are  all the  variable ports.
   For A  write                              REPLACECHR(0,ENAME,'a',NULL)
   For E  write                             REPLACECHR(0,ENAME,'e',NULL)
   For I  write                              REPLACECHR(0,ENAME,'i',NULL)
   For O  write                            REPLACECHR(0,ENAME,'o',NULL)
   For U  write                           REPLACECHR(0,ENAME,'u',NULL)
   And for o/p column total_vowels_count write expression  like this
   (length(ENAME)-length(A))
   +
   (length(ENAME)-length(E))
   +
   (length(ENAME)-length(I))
   +
   (length(ENAME)-length(O))
   +
   (length(ENAME)-length(U))
3. Finally send to target.