Page 1 of 1

Merge Files on condition

PostPosted: Mon Nov 23, 2020 5:02 pm
by Khupe
Hi,

I've a requirement to merge 2 files to produce an output file based on a condition. Any help is greatly appreciated. Thanks in advance.

Please find the sample files below.


File 1 - FB/51 - Key 9,8 &  25,8 - Duplicate keys present     
===============================   
Col#1   Col#2   Col#3   Col#4
AMERICA DEEV8522    PR  DELTAOF
NA      DEEV8543    PR  DELTAOF
NA      DRY7009A    PR  VAST
EUROPE  DRY7030Y    PR  VAST
NA      DRY7033L    PR  VAST
NA      DRY7038C    PR  VAST
NA      DRY7083C    RL  VAST
AMERICA FEED7023    PR  DELTAON
AMERICA FEED7023    PA  DELTAON
EUROPE  FEED7033    PR  DELTAON
NA      FEED7037    PR  DELTAOF
NA      FEED7037    PF  DELTAOF
NA      FEED7037    PR  DELTAON
NA      FEED7037    PA  DELTAON
NA      FEED7037    PD  DELTAON
NA      FEED8744    PR  DELTAON
NA      FEED8744    SD  DELTAON
EUROPE  FEED8909    PR  DELTAON
AMERICA DEEV9522    PR  DELTAOF
AMERICA DEEV9522    SD  DELTAOF
 



File 2 - FB/51 - Key 9,8 &  25,8  - No Duplicate keys
EUROPE  DRY7030Y    PR  VAST
EUROPE  FEED7033    PR  DELTAON
EUROPE  FEED8909    PR  DELTAON
EUROPE  FEED7023    PR  DELTAON
EUROPE  FEED7037    PR  DELTAON
EUROPE  DRY7033L    PR  VAST
 


Desired Output: For matching key in File 1 & File 2, the output should contain record from F2 (just one record in o/p). Other records in F1 should remain as it is.



Desired O/p:

EUROPE  DRY7030Y    PR  VAST
EUROPE  FEED7033    PR  DELTAON
EUROPE  FEED8909    PR  DELTAON
EUROPE  FEED7023    PR  DELTAON
EUROPE  FEED7037    PR  DELTAON
EUROPE  DRY7033L    PR  VAST
AMERICA DEEV8522    PR  DELTAOF
NA      FEED7037    PR  DELTAOF
NA      FEED7037    PF  DELTAOF
NA      FEED8744    PR  DELTAON
NA      FEED8744    SD  DELTAON
AMERICA DEEV9522    PR  DELTAOF
AMERICA DEEV9522    SD  DELTAOF
NA      DEEV8543    PR  DELTAOF
NA      DRY7009A    PR  VAST
NA      DRY7038C    PR  VAST
NA      DRY7083C    RL  VAST
 

Re: Merge Files on condition

PostPosted: Mon Nov 23, 2020 7:25 pm
by sergeyken
1. Perform JOIN on your 3 key fields. Since F2 has no duplicates, it should work. May need or not to use options SORTED[,NOSEQCK] - depending on your input sort order

2. Perform removal of duplicates, by using SORT FIELDS= and SUM FIELDS=NONE


That’s it.