Page 1 of 1

Joinkey compare two files

PostPosted: Mon Sep 03, 2012 3:33 pm
by jvinoth
Hi,
I have two files LRECL=30,FB.
Need to compare both files and write matched record into one file and unmatched record into another file.
I have did that but some record is missing in file2 where that record is present in file1.


please tell me how to get the missing record from file1 which is not present in file2.

my sort card

SORT FIELDS=COPY                                         
JOINKEYS FILES=F1,FIELDS=(12,29,A)                       
JOINKEYS FILES=F2,FIELDS=(12,29,A)                       
JOIN UNPAIRED,F1,F2                                     
REFORMAT FIELDS=(F1:12,29,F2:12,29)                     
OUTFIL FILES=01,INCLUDE=(31,1,CH,NE,X'40'),BUILD=(1,30) 
OUTFIL FILES=02,INCLUDE=(31,1,CH,EQ,X'40'),BUILD=(1,30)

Re: joinkey files compare

PostPosted: Mon Sep 03, 2012 4:21 pm
by bodatrinadh
Hi Vinoth,

You can try this...
SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(12,29,A)
JOINKEYS FILES=F2,FIELDS=(12,29,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:12,29,F2:12,29)
OUTFIL FILES=01,INCLUDE=(1,1,CH,NE,X'40',&,30,1,ch,ne,X'40'),BUILD=(1,30)  ==> Matched Records
OUTFIL FILES=02,INCLUDE=(1,1,CH,NE,X'40',&,30,1,ch,EQ,X'40'),BUILD=(1,30)  ==> Present in file1 not in file2.

Re: joinkey files compare

PostPosted: Mon Sep 03, 2012 4:36 pm
by jvinoth
thanks bodariadh..is it pssible to get the unmatched record from from both the files and write it into another file...thanks for your quik reply..

Re: joinkey files compare

PostPosted: Mon Sep 03, 2012 5:14 pm
by bodatrinadh
Yes. It is possible...

SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(12,29,A)
JOINKEYS FILES=F2,FIELDS=(12,29,A)
JOIN UNPAIRED,F1,F2,ONLY   ==> this will get Unmatched records from both the files.

Re: joinkey files compare

PostPosted: Mon Sep 03, 2012 5:52 pm
by jvinoth
thank you..cant we include in before sort case....i mean in unpaired f1,f2..
Matched record in one file
Unmatched record in second file
record present in file1 not in fil2 into third file
record present in file2 not in file1 into fourth file

like this can we...

Re: joinkey files compare

PostPosted: Mon Sep 03, 2012 7:37 pm
by BillyBoyo
You only have three cases. Matched, Unmatched F1, Unmatched F2.

If you want an "unmatched" file with records from F1 and F2, you'll probably need an indicator to know which record is from which file afterwarrds.

Re: joinkey files compare

PostPosted: Thu Oct 11, 2012 6:15 pm
by skittlebombs
I am in need of matching F1 to F2 keeping both unpaired and paired from F1. I have a sample of the input below. The first 2 records should be in the umpaired output-(NOMATCH). The last record should be in the paired output (MATCH), based on the joinkeys, 2 key fields.
JOINKEYS FILES=F1,FIELDS=(1,2,A,4,15,A)
JOINKEYS FILES=F2,FIELDS=(23,2,A,25,15,A)

This is all I know to code, as anything else after this is as clear as mud to me. I have executed multiple attempts that yeilded nothing as I am not understanding the reformat fields. I was tring to get the output that is matched and unmatched to contain the entire record 1 - 210, but I am not sure this can be done in this same step, or if the output/s would only contain the key information. Please be kind to my lack of knowledge, and I thank you for any response to this as I need it (like everyone) asap.

//SORT1 EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD *
XL XAP
XL 187532
35 485100
//SORTJNF2 DD *
XLAMCAS BXLAMCAS
XL041429 BXL041429
CB485100 B35485100
//NOMATCH DD DSN=FN.AD41.FILE1.NOMATCH,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(TRK,(10,5),RLSE)
//MATCH DD DSN=FN.AD41.FILE1.MATCH,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(TRK,(10,5),RLSE)
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,2,A,4,15,A)
JOINKEYS FILES=F2,FIELDS=(23,2,A,25,15,A)
JOIN UNPAIRED,F1


Any help and explanation would be greatly appreciated,
skittlebombs

Re: joinkey files compare

PostPosted: Thu Oct 11, 2012 6:48 pm
by BillyBoyo
 REFORMAT FIELDS=(F1:1,210,F2:1,1)


This will get you your entire record file file 1 and the first byte from file 2. You then use the value of the first byte of file 2 to test for a match - if space, no match. Use OUTFIL to write to two different files.

Re: joinkey files compare

PostPosted: Fri Oct 12, 2012 12:41 am
by skittlebombs
Wonderful! This is just what was needed!
I am now getting the expected results.

you guys rock.