Informatica
Scenario Based Interview Problems with Answers - Part 2
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 |
No comments:
Post a Comment