Exercise 6: How to replace Null with Value

Exercise 6: How to replace NULL with value and achieve target below?

CASE 1
(Source)
ins_id,premium_year,premium_Amount
101,2010,4500
101,2011,NULL
101,2012,NULL
102,2010,6000
102,2011,NULL
102,2012,NULL

Note: NULL is just a representation. There is no value over there.

(Target)
ins_id,premium_year,premium_Amount
101 2010 4500
101 2011 4500
101 2012 4500
102 2010 6000
102 2011 6000
102 2012 6000

Theory/Analysis: After analyzing source and target we can easily conclude that the premium amount value of each id will be repeating for each and every row of that id So if, some how we can store the Premium_Amount first value for each ID and pass it for each and every row then we are done.

Practically:
Step 1
Drag and Drop your source and Target Instance

Step 2
Expression
Create Three ports

PREMIUM_COMPARE (Variable Port) = IIF(ISNULL(premium_Amount),OLD_PREMIUM,premium_Amount)

OLD_PREMIUM (Variale Port) = PREMIUM_COMPARE

O_PREMIUM_AMOUNT (Output Port) = PREMIUM_COMPARE

Step 3
Connect your target from expression. Only one thing needs to take care while connecting Premium_Amount column connect it through O_PREMIUM_AMOUNT port.

That's it... Wow!!! It's was this much easy completed in only three steps.

BINGO!!!

CASE 2
(Source)
ins_id,premium_year,premium_Amount
101,2010,NULL
101,2011,4500
101,2012,NULL
102,2010,NULL
102,2011,6000
102,2012,NULL

Note: NULL is just a representation. There is no value over there.

(Target)
ins_id,premium_year,premium_Amount
101 2010 4500
101 2011 4500
101 2012 4500
102 2010 6000
102 2011 6000
102 2012 6000

Theory/Analysis: After analyzing source and target we can easily conclude that the premium amount value of each id will be repeating for each and every row of that id but here value is coming in second row not in first row then it's quite little bit challenging and our CASE 1 solution does not work here. We have to think about some other logic.

Practically:

Step 1
Drag and Drop your source and Target Instance

Step 2
Aggregator
Connect ins_id and Premium_Amount from Source Qualifier
Create One Port

O_Premium_Amount (Output Port) = MAX(Premium_Amount)

Step 3
Sorter
Connect Ins_Id and Premium_Year from Source Qualifier

Select Ins_Id as key port.

Ins_ID (key)

(Clarification: We need sorter here as we are going to join two pipeline of same source qualifier and for this we need to pass sorted input for Joiner Transformation.)

Step 4
Joiner
Connect Ins_Id and Premium_Year from Sorter and Ins_Id, O_Premium_Amount from Aggregator

Set Properties below...

Condition Tab

Ins_Id 1 = Ins_Id

Join Type: Normal

Step 5
Connect Ins_Id and Premium_Year and Premium_Amount from Joiner to Target.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.

hpandey.harsh@gmail.com