I have a scenario to fetch few records out of multiple records in database.
Source records below pertains to One account holder.
|Primary Or Secondary||Duplicate_Account_ID||Account_ID||START_DATE||END_DATE|
Out of these 4 records I have to apply below rules to get only one target record.
1. If Duplicate_Account_ID=Account_ID(then it is PRIMARY record) and if its END_DATE IS NULL then populate that record and look no further.
2. If END_DATE IS NULL in Step 1 , then go to Secondary record where Duplicate_Account_ID<>Account_ID and see if any record has NULL END_DATE. If found then populate that record.
3. If no records found in Step 2, then go back to Primary record and get the record that has the Maximum START_DATE.
I am implementing this in Pentaho and am a newbie in Pentaho. Any help pls.