Tutorial corner

Informatica,ETL,oracle,sql/plsql

Replacing NULL Value with prev col value in informatica

Spread the love

Expression transformation scenarios : We will be ending this weekend with another illustrative informatica scenario based question. We will see how to use expression transformation in informatica to solve complex scenarios we faced in our work. We just need to play with expression of each port and it will be surely help in building your informatica mapping logic skills :).

Expression transformation scenarios

Expression transformation scenarios

Expression transformation scenarios

We have an output table that has 5 columns all strings(30).  The output must not contain any null values, and the input will contain a varying number of values for the inputs and it can contains null value as well .

For each row ,we need to replace all such null values with previous column value.

Source Table

COL1 COL2 COL3 COL4 COL5
0002001 00034562 00034221 00012340
00012345 00012390 000013345
0012345 00012390

Target Table

COL1 COL2 COL3 COL4 COL5
0002001 00034562 00034221 00012340 00012340
00012345 00012390 000013345 000013345 000013345
0012345 00012390 00012390 00012390 00012390

So if you see you notice :-

  • For row #1 , we are populating COL5 with the help of COL4 value i.e.  00012340
  • For row #2 , we are populating COL4, COL5 with the help of COL3 value i.e. 000013345
  • For row #2 , we are populating COL3. COL4, COL5 with the help of COL2 value i.e. 00012390

Check here for Fibonacci series in informatica

Assumption :- We are assuming that COL1 will have some values

We will be needing one Expression transformation between the Source and Target definition . In expression transformation we can add the logic like below.

Informatica Solution 1

We can define 5 input port  i_COL field to receive values and  5  output o_COL value which will be containing the final output values.

Now for 5 output ports we can add expression logic as below .

o_COL2 =

DECODE(TRUE ,

ISNULL(i_COL2), i_COL1,

i_COL2)

———————————————————————————————————–

o_COL3 =

DECODE(TRUE ,

ISNULL(i_COL3) and NOT ISNULL(i_COL2)  , i_COL2,

ISNULL(i_COL3) and ISNULL(i_COL2)  , i_COL1,

i_COL3)

———————————————————————————————————–

o_COL4 =

DECODE(TRUE ,

ISNULL(i_COL4)  and NOT  ISNULL(i_COL3)  , i_COL3,

ISNULL(i_COL4)  and ISNULL(i_COL3) and NOT ISNULL(i_COL2)  , i_COL2,

ISNULL(i_COL4)  and ISNULL(i_COL3) and ISNULL(i_COL2)  , i_COL1,

i_COL4)

———————————————————————————————————–

o_COL5 =

DECODE(TRUE ,

ISNULL(i_COL5)  and NOT  ISNULL(i_COL4)  , i_COL4,

ISNULL(i_COL5)  and ISNULL(i_COL4) and NOT  ISNULL(i_COL3) , i_COL3,

ISNULL(i_COL5)  and ISNULL(i_COL4) and ISNULL(i_COL3) and NOT ISNULL(i_COL2), i_COL2,

ISNULL(i_COL5)  and ISNULL(i_COL4) and ISNULL(i_COL3) and ISNULL(i_COL2)  and NOT ISNULL(i_COL1), i_COL1,

i_COL5)

 

Informatica Solution 2

If you look at those condition it some how seems to be very difficult to read and implement as well. We can take help of variable port in expression to make our life easier.

One thing will come in  your mind that you can use a variable and keep on updating for each ports. But remember one variable port is processed only one time for 1 row regardless of number of ports expression have.

So to help us  ,we will be using 5 extra variable ports also and the expression will be like below.

v_COL1 = i_COL1

o_COL1=v_COL1

v_COL2 = IIF(ISNULL(i_COL2) ,v_COL1, i_COL2)

o_COL2=v_COL2

v_COL3 = IIF(ISNULL(i_COL3) ,v_COL2, i_COL3)

o_COL3=v_COL3

v_COL4 = IIF(ISNULL(i_COL4) ,v_COL3, i_COL4)

o_COL4=v_COL4

v_COL5 = IIF(ISNULL(i_COL5) ,v_COL4, i_COL5)

o_COL5=v_COL5

So here we use immediate previous port only instead of looking all previous ports. This solution is much easier to implement when we  have more no of such cols.

Informatica Solution 3

We can use java transformation also and in that case we just need to use only 1 variable while processing all the columns .

Check here for expression transformation in informatica

So we have gone through expression transformation scenarios in informatica where we replace null value with immediate not null values.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme