How to generate sequence numbers using expression
transformation?
In the expression
transformation, create a variable port and increment it by 1. Then assign the
variable port to an output port. In the expression transformation, the ports
are:
V_count=V_count+1
O_count=V_count
Design a mapping to load the first 3 rows from a flat file into a target?
You have to assign row numbers to each record. Generate the row numbers either
using the expression transformation as mentioned above or use sequence generator
transformation.
Then pass the output to filter transformation and specify the
filter condition as O_count <=3
Design a mapping to load the last 3 rows from a flat file into a target?
Consider the source has the following data.
col
a
b
c
d
e
Step1: You have to assign row numbers to each record. Generate
the row numbers using the expression transformation as mentioned above and call
the row number generated port as O_count. Create a DUMMY output port in the same
expression transformation and assign 1 to that port. So that, the DUMMY output
port always return 1 for each row.
In the expression transformation, the
ports are
V_count=V_count+1
O_count=V_count
O_dummy=1
The output
of expression transformation will be
col, o_count, o_dummy
a, 1, 1
b,
2, 1
c, 3, 1
d, 4, 1
e, 5, 1
Step2: Pass the output of expression transformation to
aggregator and do not specify any group by condition. Create an output port
O_total_records in the aggregator and assign O_count port to it. The aggregator
will return the last row by default. The output of aggregator contains the DUMMY
port which has value 1 and O_total_records port which has the value of total
number of records in the source.
In the aggregator transformation, the
ports are
O_dummy
O_count
O_total_records=O_count
The output of
aggregator transformation will be
O_total_records, O_dummy
5,
1
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.
In the joiner transformation, the join condition will be
O_dummy (port
from aggregator transformation) = O_dummy (port from expression
transformation)
The output of joiner transformation will be
col,
o_count, o_total_records
a, 1, 5
b, 2, 5
c, 3, 5
d, 4, 5
e, 5,
5
Step4: Now pass the ouput of joiner transformation to
filter transformation and specify the filter condition as O_total_records (port
from aggregator)-O_count(port from expression) <=2
In the filter
transformation, the filter condition will be
O_total_records - O_count
<=2
The output of filter transformation will be
col o_count,
o_total_records
c, 3, 5
d, 4, 5
e, 5, 5
Design a mapping to load the first record from a flat file into one table A, the
last record from a flat file into table B and the remaining records into table
C?
This is similar to the above problem; the first 3 steps are same. In the last
step instead of using the filter transformation, you have to use router
transformation. In the router transformation create two output groups.
In
the first group, the condition should be O_count=1 and connect the corresponding
output group to table A. In the second group, the condition should be
O_count=O_total_records and connect the corresponding output group to table B.
The output of default group should be connected to table C.
Consider the following products data which contain duplicate
rows.
A
B
C
C
B
D
B
Q1. Design a mapping to load all unique products in one table
and the duplicate rows in another table.
The first table should contain the
following output
A
D
The second target should contain the following
output
B
B
B
C
C
sorter transformation and sort the products data. Pass the output to an
expression transformation and create a dummy port O_dummy and assign 1 to that
port. So that, the DUMMY output port always return 1 for each row.
The
output of expression transformation will be
Product, O_dummy
A, 1
B,
1
B, 1
B, 1
C, 1
C, 1
D, 1
Pass the output of expression
transformation to an aggregator transformation. Check the group by on product
port. In the aggreagtor, create an output port O_count_of_each_product and write
an expression count(product).
The output of aggregator will be
Product, O_count_of_each_product
A,
1
B, 3
C, 2
D, 1
Now pass the output of expression
transformation, aggregator transformation to joiner transformation and join on
the products port. In the joiner transformation check the property sorted input,
then only you can connect both expression and aggregator to joiner
transformation.
The output of joiner will be
product, O_dummy,
O_count_of_each_product
A, 1, 1
B, 1, 3
B, 1, 3
B, 1, 3
C, 1,
2
C, 1, 2
D, 1, 1
Now pass the output of joiner to a router transformation, create one group
and specify the group condition as O_dummy=O_count_of_each_product. Then connect
this group to one table. Connect the output of default group to another
table.
Q2. Design a mapping to load each product once into one table
and the remaining products which are duplicated into another table.
The first
table should contain the following output
A
B
C
D
The second
table should contain the following output
B
B
C
Use sorter transformation and sort the products data. Pass the output to an
expression transformation and create a variable port,V_curr_product, and assign
product port to it. Then create a V_count port and in the expression editor
write IIF(V_curr_product=V_prev_product, V_count+1,1). Create one more variable
port V_prev_port and assign product port to it. Now create an output port
O_count port and assign V_count port to it.
In the expression
transformation, the ports
are
Product
V_curr_product=product
V_count=IIF(V_curr_product=V_prev_product,V_count+1,1)
V_prev_product=product
O_count=V_count
The output of expression transformation will be
Product, O_count
A,
1
B, 1
B, 2
B, 3
C, 1
C, 2
D, 1
Now Pass the output of
expression transformation to a router transformation, create one group and
specify the condition as O_count=1. Then connect this group to one table.
Connect the output of default group to another table.
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
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
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
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,
11000Step1: 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.
Click below links for more Informatica Interview Questions and Answers
Informatica Interview 1
Informatica Interview 2
Informatica Interview 4
No comments:
Post a Comment