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
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.
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 .
ISNULL(i_COL3) and NOT ISNULL(i_COL2) , i_COL2,
ISNULL(i_COL3) and ISNULL(i_COL2) , i_COL1,
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,
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,
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
v_COL2 = IIF(ISNULL(i_COL2) ,v_COL1, i_COL2)
v_COL3 = IIF(ISNULL(i_COL3) ,v_COL2, i_COL3)
v_COL4 = IIF(ISNULL(i_COL4) ,v_COL3, i_COL4)
v_COL5 = IIF(ISNULL(i_COL5) ,v_COL4, i_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.