Day6 Scenario Answer

              INFORMATICA SCENARIOS

1.Design a mapping to get the next row salary for the current row. If there is no next row for the current row, then the next row salary should be displayed as null.
The output should look like as.
 employee_id, salary, next_row_salary
10, 1000, 2000
20, 2000, 3000
30, 3000, 5000
40, 5000, Null

In the first expression transformation, the ports will be

employee_id 
salary
V_count=V_count+1
O_count=V_count

 In the second expression transformation, the ports will be

employee_id 
salary
V_count=V_count+1 
O_count=V_count-1
 
Join on condition O_count=O_count1.

Make sure to turn on the property ,'sorted input' In joiner transformation.


2.Design a mapping to find the sum of salaries of all employees and this sum should repeat for all the rows.
The output should look like as

employee_id, salary, salary_sum
10, 1000, 11000
20, 2000, 11000
30, 3000, 11000
40, 5000, 11000

In the expression transformation, the ports will be

employee_id

salary

O_dummy=1

In the aggregator transformation, the ports will be

salary

O_dummy

O_sum_salary=SUM(salary)

Join on condition O_dummy=O_dummy1.

Check the sorted input property of joiner.



3.Consider the following employees table as source

department_no, employee_name
20, R
10, A
10, D
20, P
10, B
10, C
20, Q
20, S

Design a mapping to load a target table with the following values from the above source?

department_no, employee_list
10, A
10, A,B
10, A,B,C
10, A,B,C,D
20, A,B,C,D,P
20, A,B,C,D,P,Q
20, A,B,C,D,P,Q,R
20, A,B,C,D,P,Q,R,S

In the expression transformation, the ports will be

department_no
employee_name
V_employee_list = IIF(ISNULL(V_employee_list),employee_name,V_employee_list||','||employee_name)
O_employee_list = V_employee_list

4.Design a mapping to load a target table with the following values from the above source?

department_no, employee_list
10, A
10, A,B
10, A,B,C
10, A,B,C,D
20, P
20, P,Q
20, P,Q,R
20, P,Q,R,S

Add below ports in expression transformation for above mapping.

In the expression transformation, the ports will be

department_no
employee_name
V_curr_deptno=department_no
V_employee_list = IIF(V_curr_deptno! = V_prev_deptno,employee_name,V_employee_list||','||employee_name)
V_prev_deptno=department_no
O_employee_list = V_employee_list

5.Design a mapping to load a target table with the following values from the above source?

department_no, employee_names
10, A,B,C,D
20, P,Q,R,S

Add aggregator tarnsformation after expression transformation and group by department_no.