Q 1. How to generate sequence / incremental numbers in Informatica?
Solution 1: 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
Solution 2: Insert a sequence generator transformation drag NEXTVAL port from sequence generator to expression.This port will give you the incremental numbers.
Use Start Value property to 1 and Increment By property to 1 for a series like 1,2,3,4,5,6,7,8.....
Q 2. Design a Informatica mapping to load the First Half rows from a Relational table into a target?
Solution: In case of relational table you can go to source qualifier go to property and write the SQL query like
SELECT * from EMP
WHERE ROWNUM <= (SELECT Count(*)/2 FROM EMP)
No additional filter required connect all ports to other transformation and finally to target, then run the mapping.
Q 3. Design a Informatica mapping to load the Second Half rows from a Relational table into a target?
Solution: In case of relational table you can go to source qualifier go to property and write the SQL query like
SELECT * from EMP
MINUS
SELECT * from EMP
WHERE ROWNUM <= (SELECT Count(*)/2 FROM EMP)
No additional filter required connect all ports to other transformation and finally to target, then run the mapping.
Q 4. Design a Informatica mapping to load the first 4 rows from a flat file into a target?
Solution: Now that the above query wont work for a flat file source then 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 ROW_NUMBER <=4
| ID | COL1 | COL2 | ROW_NUMBER |
| 1 | A | 10 | 1 |
| 2 | B | 20 | 2 |
| 3 | C | 30 | 3 |
| 4 | D | 10 | 4 |
| 5 | E | 30 | 5 |
| 6 | F | 10 | 6 |
Q 5. Design a Informatica mapping to load the last 3 rows from a flat file into a target?
Solution: Consider the source has the following data.
COLUMN
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 output 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 |
Q 6. Design a Informatica mapping to load record from a flat file, first record into one table A, the last record from the flat file into table B and the remaining records into table C?
Solution: This is similar to the above problem; the first three steps are same. In the last step instead of using the filter transformation, you have to use router transformation. In the router transformation you have to 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.
Q 7. Consider the following source Data.
A
B
C
C
B
D
B
Q 7.1 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
Solution: Use 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 aggregator, 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 |
| C | 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.
Q 7.2 Design a Informatica mapping to load original and duplicate records in two different tables / Separating duplicate and non-duplicate rows.
The first table should contain the following output
A
B
C
D
The second table should contain the following output
B
B
C
Solution1: Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a variable port,V_Current_product, and assign product port to it. Then create a V_Count port and in the expression editor write V_Count=IIF(V_Current_product=V_Previous_product,V_Count+1,1). Create one more variable port V_Previous_product 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_Current_product=product
V_Count=IIF(V_Current_product=V_Previous_product,V_Count+1,1)
V_Previous_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.
Solution2: Next approach to do this is use aggregator, First sort the data using sorter then connect it to aggregator transformation group by product with an additional port COUNT_RECORD in expression put COUNT(Product).
Now with a router transformation create two groups namely DUPLICATE & ORIGINAL and give the group condition COUNT_RECORD > 1 & COUNT_RECORD =1 respectively.
In the Aggregator transformation ports are
Product
COUNT_RECORD=COUNT(Product)
In the Router Transformation group Conditions are
DUPLICATE group (COUNT_RECORD>1)
ORIGINAL group (COUNT_RECORD=1)
Connect Table 1 to DUPLICATE group and Table 2 to Original Group. There you go you have duplicate and original data separated.
Q 8. Let us assume the following Sales Data
| ID | PERSONAL_SALES |
| 10 | 40000 |
| 20 | 80000 |
| 30 | 40000 |
| 40 | 60000 |
| 50 | NULL |
| 60 | 50000 |
Q 8.1. Design a Informatica mapping to calculate the cumulative sum of PERSONAL_SALES and load that into target table?
The Expected Output in target should be
| ID | PERSONAL_SALES | O_RETURN_VALUE |
| 10 | 40000 | 40000 |
| 20 | 80000 | 120000 |
| 30 | 40000 | 160000 |
| 40 | 60000 | 220000 |
| 50 | NULL | 220000 |
| 60 | 50000 | 270000 |
Solution:
Connect the source Qualifier to expression transformation.In the expression transformation create a variable port named V_RETURN_VALUE and write the expression CUME(PERSONAL_SALES), again create a output port O_RETURN_VALUE and assign the expression V_RETURN_VALUE to it. Connect all the output ports to target and run the mapping.
In the expression transformation,the ports are
ID
PERSONAL_SALES V_RETURN_VALUE = CUME(PERSONAL_SALES) O_RETURN_VALUE = V_RETURN_VALUE |
// Input/Output Port
// Input/Output Port // Variable Port // Output Port |
Connect the output port from the expression transformation to the target and see the results.
Q 8.2. Design a Informatica mapping to calculate the Moving sum of PERSONAL_SALES for two rows and load that into target table?
The Expected Output in target should be
| ID | PERSONAL_SALES | O_RETURN_VALUE |
| 10 | 40000 | NULL |
| 20 | 80000 | 120000 |
| 30 | 40000 | 120000 |
| 40 | 60000 | 100000 |
| 50 | NULL | 60000 |
| 60 | 50000 | 50000 |
Solution:
Connect the source Qualifier to expression transformation.In the expression transformation create a variable port named V_RETURN_VALUE and write the expression MOVINGSUM(PERSONAL_SALES,2) again create a output port O_RETURN_VALUE and assign the expression V_RETURN_VALUE to it. Connect all the output ports to target and run the mapping.
In the expression transformation,the ports are
ID
PERSONAL_SALES V_RETURN_VALUE = MOVINGSUM(PERSONAL_SALES,2) O_RETURN_VALUE = V_RETURN_VALUE |
// Input/Output Port
// Input/Output Port // Variable Port // Output Port |
Connect the output port to the target and see the results
Q 8.3. Design a Informatica mapping to get the previous row value for the current row. If there is no previous row then load NULL as in case of first row there is no previous row
The Expected Output in target should be
| ID | PERSONAL_SALES | O_RETURN_VALUE |
| 10 | 40000 | NULL |
| 20 | 80000 | 40000 |
| 30 | 40000 | 80000 |
| 40 | 60000 | 40000 |
| 50 | NULL | 60000 |
| 60 | 50000 | NULL |
Solution:
Connect the source Qualifier to expression transformation. In the expression transformation, create a variable port V_COUNT and start incrementing it by one for each row entering the expression transformation. Also create V_SALES variable port and assign the expression IIF(V_COUNT=1,NULL,V_PREV_SALES) to it . Then Create one more variable port V_PREV_SALES and assign PERSONAL_SALES to it. Now create output port O_RETURN_VALUE and assign V_SALES to it. Connect the expression transformation to the target ports and run the mapping after creation of precedding session and workflow.
In the expression transformation, the ports will be
ID
PERSONAL_SALES V_COUNT=V_COUNT+1 V_SALES=IIF(V_COUNT=1,NULL,V_PREV_SALES) V_PREV_SALES=PERSONAL_SALES O_RETURN_VALUE=V_SALES |
// Input/Output Port
// Input/Output Port // Variable Port // Variable Port // Variable Port // Output Port |
Q 8.4. Design a mapping to get the next row Sales 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
| ID | PERSONAL_SALES | O_RETURN_VALUE |
| 10 | 40000 | 80000 |
| 20 | 80000 | 40000 |
| 30 | 40000 | 60000 |
| 40 | 60000 | NULL |
| 50 | NULL | 50000 |
Solution:
1. Create a source qualifier transformation and connect it to two separate expression transformation. In each of the expression transformation create a variable port V_COUNT and increment it by 1 using expression V_COUNT+1. Then create an output port O_COUNT in each expression transformation. Assign the value V_COUNT in 1st expression and V_COUNT-1 in the 2nd expression as shown below.
In the 1st expression transformation, the ports will be
ID
PERSONAL_SALES V_COUNT=V_COUNT+1 O_COUNT=V_COUNT |
// Input/Output Port
// Input/Output Port // Variable Port // Output Port |
1st Expression Data will look like
| ID | PERSONAL_SALES | O_COUNT |
| 10 | 40000 | 1 |
| 20 | 80000 | 2 |
| 30 | 40000 | 3 |
| 40 | 60000 | 4 |
| 50 | NULL | 5 |
In the 2nd expression transformation, the ports will be
ID
PERSONAL_SALES V_COUNT=V_COUNT+1 O_COUNT=V_COUNT-1 |
// Input/Output Port
// Input/Output Port // Variable Port // Output Port |
2nd Expression Data will look like
| ID | PERSONAL_SALES | O_COUNT |
| 10 | 40000 | 0 |
| 20 | 80000 | 1 |
| 30 | 40000 | 2 |
| 40 | 60000 | 3 |
| 50 | NULL | 4 |
3. Pass the output of joiner transformation to a target table. In the Joiner transformation, connect the ID and PERSONAL_SALES from the 1st expression transformation to the ID and PERSONAL_SALES in target table. Connect the PERSONAL_SALES which is coming from the 2nd expression transformation to the next row sales port in the target table.
Q 8.5. Design a Informatica Mapping to find the sum of all sales of an employee id and this sum should come in another column for all rows.
The output should look like as
| ID | PERSONAL_SALES | O_RETURN_VALUE |
| 10 | 40000 | 270000 |
| 20 | 80000 | 270000 |
| 30 | 40000 | 270000 |
| 40 | 60000 | 270000 |
| 50 | NULL | 270000 |
| 60 | 50000 | 270000 |
Solution:
Create a source qualifier transformation and connect it to the Aggregator transformation.In aggregator transformation create a Output port O_RETURN_VALUE and assign expression SUM(PERSONAL_SALES). Don not specify and group by for any filed and then connect all the other output ports to target and runt the mapping.
In the Aggregator transformation, the ports will be
ID
PERSONAL_SALES O_RETURN_VALUE=SUM(PERSONAL_SALES) |
// Input/Output Port
// Input/Output Port // Output Port |
