Hi All,

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
Primary 1000 1000 26/05/2017 NULL
Primary 1000 1000 10/03/2014 25/05/2017
Secondary 2000 1000 26/05/2017 NULL
Secondary 3000 1000 10/03/2014 25/05/2017
SOURCE_CODE TARGET_CODE(KEY) START_DATE END_DATE
100000-127 100000-127 26/05/2017 0:00 NULL
100000-127 100000-127 10/03/2014 0:00 25/05/2017 0:00
47971-168 100000-127 26/05/2017 0:00 NULL
47971-168 100000-127 10/03/2014 0:00 25/05/2017 0:00



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.