1. Consider the following employees data as source
employee_id, salary
10, 1000
20, 2000
30, 3000
40, 5000
Q1. Design a mapping to load the cumulative sum of salaries of employees into target table?
The target table data should look like as
employee_id, salary, cumulative_sum
10, 1000, 1000
20, 2000, 3000
30, 3000, 6000
40, 5000, 11000
Solution:
Connect the source Qualifier to expression transformation. In the
expression transformation, create a variable port V_cum_sal and in the
expression editor write V_cum_sal+salary. Create an output port
O_cum_sal and assign V_cum_sal to it.
Q2. Design a mapping to get the pervious row salary
for the current row. If there is no pervious row exists for the current
row, then the pervious row salary should be displayed as null.
The output should look like as
employee_id, salary, pre_row_salary
10, 1000, Null
20, 2000, 1000
30, 3000, 2000
40, 5000, 3000
Solution:
Connect the source Qualifier to expression transformation. In the
expression transformation, create a variable port V_count and increment
it by one for each row entering the expression transformation. Also
create V_salary variable port and assign the expression
IIF(V_count=1,NULL,V_prev_salary) to it . Then create one more variable
port V_prev_salary and assign Salary to it. Now create output port
O_prev_salary and assign V_salary to it. Connect the expression
transformation to the target ports.
In the expression transformation, the ports will be
employee_id
salary
V_count=V_count+1
V_salary=IIF(V_count=1,NULL,V_prev_salary)
V_prev_salary=salary
O_prev_salary=V_salary
Q3. 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
Solution:
Step1: Connect the source qualifier to two
expression transformation. In each expression transformation, create a
variable port V_count and in the expression editor write V_count+1. Now
create an output port O_count in each expression transformation. In the
first expression transformation, assign V_count to O_count. In the
second expression transformation assign V_count-1 to O_count.
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
Step2: Connect both the expression transformations
to joiner transformation and join them on the port O_count. Consider the
first expression transformation as Master and second one as detail. In
the joiner specify the join type as Detail Outer Join. In the joiner
transformation check the property sorted input, then only you can
connect both expression transformations to joiner transformation.
Step3: Pass the output of joiner transformation to a
target table. From the joiner, connect the employee_id, salary which
are obtained from the first expression transformation to the
employee_id, salary ports in target table. Then from the joiner, connect
the salary which is obtained from the second expression transformaiton
to the next_row_salary port in the target table.
Q4. 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
Solution:
Step1: Connect the source qualifier to the
expression transformation. In the expression transformation, create a
dummy port and assign value 1 to it.
In the expression transformation, the ports will be
employee_id
salary
O_dummy=1
Step2: Pass the output of expression transformation
to aggregator. Create a new port O_sum_salary and in the expression
editor write SUM(salary). Do not specify group by on any port.
In the aggregator transformation, the ports will be
salary
O_dummy
O_sum_salary=SUM(salary)
Step3: Pass the output of expression transformation,
aggregator transformation to joiner transformation and join on the
DUMMY port. In the joiner transformation check the property sorted
input, then only you can connect both expression and aggregator to
joiner transformation.
Step4: Pass the output of joiner to the target table.
2. 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
Q1. 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
Solution:
Step1: Use a sorter transformation and sort the data
using the sort key as department_no and then pass the output to the
expression transformation. 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
Step2: Now connect the expression transformation to a target table.
Q2. 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
Solution:
Step1: Use a sorter transformation and sort the data
using the sort key as department_no and then pass the output to the
expression transformation. 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
Step2: Now connect the expression transformation to a target table.
Q3. 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
Solution:
The first step is same as the above problem. Pass the output of
expression to an aggregator transformation and specify the group by as
department_no. Now connect the aggregator transformation to a target
table.
For more Visit
http://www.quontrasolutions.com/blog/category/informatica/
![clip_image014[1]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhD5d-P7WRDHhyEJrV998wuagAu5KGIdbUkSilKrZLvULABlkHmDAUmRu0N_pzZzrPCwQqBSzF4sO-3ebe8l98LKeq9gSP2KMN2Fj18oZ5QF493seqX2Zonza6ekkrUi5iQqqURuNJVZQ2s/s1600-h/clip_image014%5B1%5D%5B2%5D.gif "clip_image014[1]"){kind=small}
