I am posting Informatica complex scenarios questions in this post and I will post Informatica scenarios answers in my next post. The follow informarica scenarios will be useful for attending interview also to practice for handling different scenarios in day to day office work.
Scenario 1:
I have Source as a Table and I want to load the data in Flat file Target with one Header and one Trailer
Header: (emp,sysdate)
Trailer: count(records), sum(salary)
How will be the approach in Informatica
Scenario 2:
Hi all,
My source,
Table 1
null 11
null 11
Table 2
66 1
67 2
The output I need is
null 11 66 1
null 11 67 2
so for the above requirement i have tried with full outer join.But that is not worked for me.
Scenario 3:
Source Row is
Column A Column B
......................
ABC 3
XYZ 2
Target should come as
Column A Column B
........................
ABC 3
ABC 3
ABC 3
XYZ 2
XYZ 2
Scenario 4:
I have a source table as below
a1
a2
a3
a4
a5
a6
a7
a8
a9
Need an output as
a1,a2,a3
a4,a5,a6
a7,a8,a9
Scenario 5;
Without using aggregator transformation calculate sum and average of sal by dept wise.
Scenario 6:
We have a target source table containing 3 columns : Col1, Col2 and Col3. There is only 1 row in the table as follows:
Col1 Col2 Col3
-----------------
a b c
There is target table containg only 1 column Col. Design a mapping so that the target table contains 3 rows as follows:
Col
-----
a
b
c
Scenario 7:
There is a source table that contains duplicate rows.Design a mapping to load all the unique rows in 1 target while all the duplicate rows (only 1 occurence) in another target.
Scenario 8:
There is a source table containing 2 columns Col1 and Col2 with data as follows:
Col1 Col2
a l
b p
a m
a n
b q
x y
Design a mapping to load a target table with following values from the above mentioned source:
Col1 Col2
a l,m,n
b p,q
x y
Scenario 9:
Design an Informatica mapping to load first half records to 1 target while other half records to a separate target.
Scenario 10:
How to generate sequence numbers using expression transformation?
Scenario 11:
Design a mapping to load the first 3 rows from a flat file into a target?
Scenario 12:
Design a mapping to load the last 3 rows from a flat file into a target?
Scenario 13:
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?
Scenario 14:
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
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
Scenario 15:
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
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
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
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
Scenario 16:
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
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
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
Scenario 17:
Consider the following product types data as the source.
Product_id, product_type
10, video
10, Audio
20, Audio
30, Audio
40, Audio
50, Audio
10, Movie
20, Movie
30, Movie
40, Movie
50, Movie
60, Movie
Assume that there are only 3 product types are available in the source. The source contains 12 records and you dont know how many products are available in each product type.
Q1. Design a mapping to select 9 products in such a way that 3 products should be selected from video, 3 products should be selected from Audio and the remaining 3 products should be selected from Movie.
Q2. In the above problem Q1, if the number of products in a particular product type are less than 3, then you wont get the total 9 records in the target table. For example, see the videos type in the source data. Now design a mapping in such way that even if the number of products in a particular product type are less than 3, then you have to get those less number of records from another porduc types. For example: If the number of products in videos are 1, then the reamaining 2 records should come from audios or movies. So, the total number of records in the target table should always be 9.
Scenario 18:
Design a mapping to convert column data into row data without using the normalizer transformation.
The source data looks like
col1, col2, col3
a, b, c
d, e, f
The target table data should look like
Col
a
b
c
d
e
f
Scenario 19:
Design a mapping to convert row data into column data.
The source data looks like
id, value
10, a
10, b
10, c
20, d
20, e
20, f
The target table data should look like
id, col1, col2, col3
10, a, b, c
20, d, e, f
Scenario 20:
c1, c2, c3, c4
A, B, D, H
A, B, D, I
A, B, E, NULL
A, C, F, NULL
A, C, G, NULL
Here in this table, column C1 is parent of column C2, column C2 is parent of column C3, column C3 is parent of column C4.
Q1. Design a mapping to load the target table with the below data. Here you need to generate sequence numbers for each element and then you have to get the parent id. As the element "A" is at root, it does not have any parent and its parent_id is NULL.
id, element, parent_id
1, A, NULL
2, B, 1
3, C, 1
4, D, 2
5, E, 2
6, F, 3
7, G, 3
8, H, 4
9, I, 4
Q2. This is an extension to the problem Q1. Let say column C2 has null for all the rows, then C1 becomes the parent of C3 and c3 is parent of C4. Let say both columns c2 and c3 has null for all the rows. Then c1 becomes the parent of c4. Design a mapping to accommodate these type of null conditions.
Scenario 21:
Input:
1,a1,null,null
1,null,a2,null
1,null,null,a3
output:
1,a1,a2,a3
1,a1,a2,a3
1,a1,a2,a3
Scenario 22:
Five sources from different client location and load the data into respective target.Design a mapping for this.
Scenario 23:
Source table
1,a1,a2,a3
1,a4,a5,a6
1,a7,a8,a9
Target Table
1|a1|a2|a3|a4|a5|a6|a7|a8|a9
Scenario 24:
Source
4
5
2
3
1
5
Target
1
2
3
4
5
5
Scenario 25:
Source:
Table1 has some records
1
2
3
4
5
Table2 has some records
2
5
4
Target:
Need output that records that present in table1 that should not present in table2.
1
3
I have Source as a Table and I want to load the data in Flat file Target with one Header and one Trailer
Header: (emp,sysdate)
Trailer: count(records), sum(salary)
How will be the approach in Informatica
Scenario 2:
Hi all,
My source,
Table 1
null 11
null 11
Table 2
66 1
67 2
The output I need is
null 11 66 1
null 11 67 2
so for the above requirement i have tried with full outer join.But that is not worked for me.
Scenario 3:
Source Row is
Column A Column B
......................
ABC 3
XYZ 2
Target should come as
Column A Column B
........................
ABC 3
ABC 3
ABC 3
XYZ 2
XYZ 2
Scenario 4:
I have a source table as below
a1
a2
a3
a4
a5
a6
a7
a8
a9
Need an output as
a1,a2,a3
a4,a5,a6
a7,a8,a9
Scenario 5;
Without using aggregator transformation calculate sum and average of sal by dept wise.
Scenario 6:
We have a target source table containing 3 columns : Col1, Col2 and Col3. There is only 1 row in the table as follows:
Col1 Col2 Col3
-----------------
a b c
There is target table containg only 1 column Col. Design a mapping so that the target table contains 3 rows as follows:
Col
-----
a
b
c
Scenario 7:
There is a source table that contains duplicate rows.Design a mapping to load all the unique rows in 1 target while all the duplicate rows (only 1 occurence) in another target.
Scenario 8:
There is a source table containing 2 columns Col1 and Col2 with data as follows:
Col1 Col2
a l
b p
a m
a n
b q
x y
Design a mapping to load a target table with following values from the above mentioned source:
Col1 Col2
a l,m,n
b p,q
x y
Scenario 9:
Design an Informatica mapping to load first half records to 1 target while other half records to a separate target.
Scenario 10:
How to generate sequence numbers using expression transformation?
Scenario 11:
Design a mapping to load the first 3 rows from a flat file into a target?
Scenario 12:
Design a mapping to load the last 3 rows from a flat file into a target?
Scenario 13:
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?
Scenario 14:
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
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
Scenario 15:
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
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
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
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
Scenario 16:
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
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
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
Scenario 17:
Consider the following product types data as the source.
Product_id, product_type
10, video
10, Audio
20, Audio
30, Audio
40, Audio
50, Audio
10, Movie
20, Movie
30, Movie
40, Movie
50, Movie
60, Movie
Assume that there are only 3 product types are available in the source. The source contains 12 records and you dont know how many products are available in each product type.
Q1. Design a mapping to select 9 products in such a way that 3 products should be selected from video, 3 products should be selected from Audio and the remaining 3 products should be selected from Movie.
Q2. In the above problem Q1, if the number of products in a particular product type are less than 3, then you wont get the total 9 records in the target table. For example, see the videos type in the source data. Now design a mapping in such way that even if the number of products in a particular product type are less than 3, then you have to get those less number of records from another porduc types. For example: If the number of products in videos are 1, then the reamaining 2 records should come from audios or movies. So, the total number of records in the target table should always be 9.
Scenario 18:
Design a mapping to convert column data into row data without using the normalizer transformation.
The source data looks like
col1, col2, col3
a, b, c
d, e, f
The target table data should look like
Col
a
b
c
d
e
f
Scenario 19:
Design a mapping to convert row data into column data.
The source data looks like
id, value
10, a
10, b
10, c
20, d
20, e
20, f
The target table data should look like
id, col1, col2, col3
10, a, b, c
20, d, e, f
Scenario 20:
c1, c2, c3, c4
A, B, D, H
A, B, D, I
A, B, E, NULL
A, C, F, NULL
A, C, G, NULL
Here in this table, column C1 is parent of column C2, column C2 is parent of column C3, column C3 is parent of column C4.
Q1. Design a mapping to load the target table with the below data. Here you need to generate sequence numbers for each element and then you have to get the parent id. As the element "A" is at root, it does not have any parent and its parent_id is NULL.
id, element, parent_id
1, A, NULL
2, B, 1
3, C, 1
4, D, 2
5, E, 2
6, F, 3
7, G, 3
8, H, 4
9, I, 4
Q2. This is an extension to the problem Q1. Let say column C2 has null for all the rows, then C1 becomes the parent of C3 and c3 is parent of C4. Let say both columns c2 and c3 has null for all the rows. Then c1 becomes the parent of c4. Design a mapping to accommodate these type of null conditions.
Scenario 21:
Input:
1,a1,null,null
1,null,a2,null
1,null,null,a3
output:
1,a1,a2,a3
1,a1,a2,a3
1,a1,a2,a3
Scenario 22:
Five sources from different client location and load the data into respective target.Design a mapping for this.
Scenario 23:
Source table
1,a1,a2,a3
1,a4,a5,a6
1,a7,a8,a9
Target Table
1|a1|a2|a3|a4|a5|a6|a7|a8|a9
Scenario 24:
Source
4
5
2
3
1
5
Target
1
2
3
4
5
5
Scenario 25:
Source:
Table1 has some records
1
2
3
4
5
Table2 has some records
2
5
4
Target:
Need output that records that present in table1 that should not present in table2.
1
3
I will keep you posting Informatica scenario answers very soon!!!
Its that possible to share informatica scenario questions and answers ?
ReplyDeleteInformatica scenario's questions are useful for me to prepare for interview
ReplyDeletePlease give answers for informmatica powercentre scenarios questions
ReplyDeleteInformatica scenarios questions will be tested on my laptop. Thanks kashif for sharing informatica scenarios questions
ReplyDeleteplease give me answers
ReplyDeletewhere can i find the answers
ReplyDelete