Informatica: Mapping to Load the first record from a flat file into one table?

Question

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?

Answer 1

Please follow the below steps

1. From source qualifier pass data to the exp1 transformation, Add an variable port as

2. V_row_number. We can assign value to variable V_row_number by two ways

   1. By using sequence generator

   2. By using below logic in the expression transformation
      V_ row_number =V_ row_number +1
      O_ row_number =V_ row_number

   Input, O_ row_number
   a, 1,
   b, 2,
   c, 3,
   d, 4,
   e, 5,

Table A - In one pipeline, send data from exp transformation to filter where you filter out first row as O_ row_number = 1 to table A.

Table B - Now again there are two ways to identify last records,

Pass all rows from exp1 transformation to agg transformation and don’t select any column in group by port,it will sent last record to table B.

By using max in agg

Table c - Now send out of step 4 to an exp2 transformation, where you will get O_ row_number=5 then add a dummy port into a same exp with value 1 now join this exp2 with the very first exp1 so that you will get output like below

Input, O_ row_number, O_ last_row_number
a,                            1,                            5
b,                            2,                            5
c,                             3,                            5
d,                            4,                            5
e,                            5,                            5

Now pass the data to filter and add condition add O_ row_number <> 1 and O_ row_number <> O_ last_row_number

Comments

Can you tell me what is this step here:   Table c - Now send out of step 4 to an exp2 transformation