Is it possible to subtract matching records from a file?



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

Is it possible to subtract matching records from a file?

Postby deucalion0 » Mon Jul 18, 2016 7:17 pm

Hello all,

So I have been trying to figure out how to subtract matching records from a dataset.

So in file 1 there would be the following

AAA
AAA
AAA
BBB
BBB
CCC
DDD
EEE

And in file 2:

AAA
AAA
BBB
EEE

So anything found in file 2 should be removed from file 1 which would leave me with:

AAA
BBB
CCC
DDD

I have been racking my brains trying to figure out how to do this, I was thinking along the lines of using sequence numbers or maybe using an OMIT, but I am not sure how to only OMIT record by record.

I have not found any examples of SORTS like this, is this even possible?

I have read the manual for a while but there is so many possibilities with DFSORT I have no idea which route to go down.

I would appreciate any advice or pointers on this.


Thanks!
deucalion0
 
Posts: 58
Joined: Thu Jul 31, 2014 3:47 pm
Has thanked: 32 times
Been thanked: 0 time

Re: Is it possible to subttract matching records from a file

Postby BillyBoyo » Mon Jul 18, 2016 9:36 pm

You are partly on the right lines. You need a JOINKEYS. In the JOINKEYS you need to temporarily append/prepend (fixed vs variable-length records) a sequence number which "restarts" on the key. This code will be in the JNF1CNTL and JNF2CNTL datasets which respectively allow amending of the F1 and F2 files prior to the JOINKEYS.

In the JOINKEYS match on the key and the sequence number. JOIN UNPAIRED,F1,ONLY will just get the mismatches, and then in the REFORMAT statement define an output record from the F1 which ignores the sequence number.

These users thanked the author BillyBoyo for the post:
deucalion0 (Tue Jul 19, 2016 8:57 pm)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Is it possible to subttract matching records from a file

Postby deucalion0 » Tue Jul 19, 2016 8:58 pm

BillyBoyo wrote:You are partly on the right lines. You need a JOINKEYS. In the JOINKEYS you need to temporarily append/prepend (fixed vs variable-length records) a sequence number which "restarts" on the key. This code will be in the JNF1CNTL and JNF2CNTL datasets which respectively allow amending of the F1 and F2 files prior to the JOINKEYS.

In the JOINKEYS match on the key and the sequence number. JOIN UNPAIRED,F1,ONLY will just get the mismatches, and then in the REFORMAT statement define an output record from the F1 which ignores the sequence number.


Hi Billy!

Thanks a lot, that was a great help and I managed to get it to work!

You saved me a lot of time thanks!!

:)
deucalion0
 
Posts: 58
Joined: Thu Jul 31, 2014 3:47 pm
Has thanked: 32 times
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post