Plz try this and give me solution to solve it through Informatica.

a) Assign priority to every application_id based on the following condition
1) IF(SUBSTR(DECISION_NM),1,4),’SALE’ and ACCT_NBR>0,then 1 else 2
2) IF(SUBSTR(DECISION_NM),1,4),’DECL’, then 3
3) IF(SUBSTR(DECISION_NM),1,4),’WITH’ ,then 4
4) IF(SUBSTR(DECISION_NM),1,4),’ ‘, then 5 else 6

b) Sort the data by finder_num and priority value (obtained from step a)

c) Let the original finder_num from source be the final finder_num for the application with highest priority.

For all the lower priority values for the same finder_num, perform step d.

d) Replace finder_num with following: ‘DU’ and application_id (CR_APPL_ID) i.e. concatenate ‘DU’ with cr_appl_id.

e) For the application with same priority, retain original finder_num for the lowest value application_id(min(cr_appl_id)) and implement the above mentioned step (step d: Replace process).

Question asked by visitor Anam