Merge Files on condition



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Merge Files on condition

Postby Khupe » Mon Nov 23, 2020 5:02 pm

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
 
Khupe
 
Posts: 2
Joined: Mon Nov 23, 2020 1:07 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Merge Files on condition

Postby sergeyken » Mon Nov 23, 2020 7:25 pm

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.

These users thanked the author sergeyken for the post:
Khupe (Tue Nov 24, 2020 11:11 am)
User avatar
sergeyken
 
Posts: 134
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 3 times
Been thanked: 11 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post