INFORMATICA SCENARIO BASED QUESTIONS

Q1. The source data contains only column 'id'. It will have sequence numbers from 1 to 1000. The source data looks like as

Id
1
2
3
4
5
6
7
8
....
1000

Create a workflow to load only the Fibonacci numbers in the target table. The target table data should look like as

 Id
1
2
3
5
8
13
.....

In Fibonacci series each subsequent number is the sum of previous two numbers. Here assume that the first two numbers of the fibonacci series are 1 and 2.

Solution:

STEP1: Drag the source to the mapping designer and then in the Source Qualifier Transformation properties, set the number of sorted ports to one. This will sort the source data in ascending order. So that we will get the numbers in sequence as 1, 2, 3, ....1000

STEP2: Connect the Source Qualifier Transformation to the Expression Transformation. In the Expression Transformation, create three variable ports and one output port. Assign the expressions to the ports as shown below.

Ports in Expression Transformation:
id
v_sum = v_prev_val1 + v_prev_val2
v_prev_val1 = IIF(id=1 or id=2,1, IIF(v_sum = id, v_prev_val2, v_prev_val1) )
v_prev_val2 = IIF(id=1 or id =2, 2, IIF(v_sum=id, v_sum, v_prev_val2) )
o_flag = IIF(id=1 or id=2,1, IIF( v_sum=id,1,0) )

STEP3: Now connect the Expression Transformation to the Filter Transformation and specify the Filter Condition as o_flag=1

STEP4: Connect the Filter Transformation to the Target Table.

Q2. The source table contains two columns "id" and "val". The source data looks like as below

id     val
1      a,b,c
2      pq,m,n
3      asz,ro,liqt

Here the "val" column contains comma delimited data and has three fields in that column.
Create a workflow to split the fields in “val” column to separate rows. The output should look like as below.

id     val
1      a
1      b
1      c
2      pq
2      m
2      n
3      asz
3      ro
3      liqt

Solution:

STEP1: Connect three Source Qualifier transformations to the Source Definition

STEP2: Now connect all the three Source Qualifier transformations to the Union Transformation. Then connect the Union Transformation to the Sorter Transformation. In the sorter transformation sort the data based on Id port in ascending order.

STEP3: Pass the output of Sorter Transformation to the Expression Transformation. The ports in Expression Transformation are:

id (input/output port)
val (input port)
v_currend_id (variable port) = id
v_count (variable port) = IIF(v_current_id!=v_previous_id,1,v_count+1)
v_previous_id (variable port) = id
o_val (output port) = DECODE(v_count, 1,
        SUBSTR(val, 1, INSTR(val,',',1,1)-1 ),
        2,
        SUBSTR(val, INSTR(val,',',1,1)+1, INSTR(val,',',1,2)-INSTR(val,',',1,1)-1),
        3,
        SUBSTR(val, INSTR(val,',',1,2)+1),
        NULL
        )

STEP4: Now pass the output of Expression Transformation to the Target definition. Connect id, o_val ports of Expression Transformation to the id, val ports of Target Definition.

1. 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.

Solution:

Step1: Use sorter transformation and sort the data using the key as product_type.

Step2: Connect the sorter transformation to an expression transformation. In the expression transformation, the ports will be

product_id
product_type
V_curr_prod_type=product_type
V_count = IIF(V_curr_prod_type = V_prev_prod_type,V_count+1,1)
V_prev_prod_type=product_type
O_count=V_count

Step3: Now connect the expression transformaion to a filter transformation and specify the filter condition as O_count<=3. Pass the output of filter to a target table.


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.

Solution:

The first two steps are same as above.

Step3: Connect the expression transformation to a sorter transformation and sort the data using the key as O_count. The ports in soter transformation will be

product_id
product_type
O_count (sort key)

Step3: Discard O_count port and connect the sorter transformation to an expression transformation. The ports in expression transformation will be

product_id
product_type
V_count=V_count+1
O_prod_count=V_count

Step4: Connect the expression to a filter transformation and specify the filter condition as O_prod_count<=9. Connect the filter transformation to a target table.

3. 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

Solution:

Step1: Use sorter transformation and sort the data using id port as the key. Then connect the sorter transformation to the expression transformation.

Step2: In the expression transformation, create the ports and assign the expressions as mentioned below.

id
value
V_curr_id=id
V_count= IIF(v_curr_id=V_prev_id,V_count+1,1)
V_prev_id=id
O_col1= IIF(V_count=1,value,NULL)
O_col2= IIF(V_count=2,value,NULL)
O_col3= IIF(V_count=3,value,NULL)

Step3: Connect the expression transformation to aggregator transformation. In the aggregator transforamtion, create the ports and assign the expressions as mentioned below.

id (specify group by on this port)
O_col1
O_col2
O_col3
col1=MAX(O_col1)
col2=MAX(O_col2)
col3=MAX(O_col3)