Matching and unmatching records

scdinesh · Posted: Tue Feb 07, 2017 12:41 am

Guys, Appreciate your help me on following requirement:
I've 2 input files(not in sorted order) , compare both files with matching key as(1st 4 digits) and create 4 output files: Output File1 with matching records only from input file1 and output File 2 with matching records only from input file2(Although key matches , I need to validate the remaining records latter from the files separately)
Output file 3 with unmatched records from input file1 and Output file 4 with unmatched records from input file2.

Sample Records
Input File1:
2222Keith
1111David
4444Sam
3333Rohit

Input File2:
3778Ruban
3333Rizwan
4444Andrew
5555Travis

Comparison upon key: S.No(1st 4 digit)
Output File1:(match from input File1)
4444Sam
3333Rohit

Output File2:(match from input File2)
3333Rizwan
4444Andrew

Output File3:(un match from input File3)
2222Keith
1111David

Output File4:(unmatch from input File4)
3778Ruban
5555Travis

Thanks for your help

sergeyken · Posted: Tue Feb 07, 2017 1:59 am

RTFM:

Bill Woodger · Posted: Tue Feb 07, 2017 2:39 am

No need for FILL=, use the match-marker (?, a question-mark, in the REFORMAT record, B=both, 1=file1 only, 2=file2 only).

The SORT would be FIELDS=COPY.

scdinesh · Posted: Tue Feb 07, 2017 2:43 am

Thanks sergeyken for quick response...
Assume my record length is set to 80 , with first 4 digits as key
may I know what is the value I can add in 'Include =' . I don't have any specific condition to filter out the records.
Also please help me on REFORMAT FIELDS=...,FILL=...

scdinesh · Posted: Tue Feb 07, 2017 2:55 am

Hi Bill,
Thanks for the reply!!!
Please advise how can I specify.

JOINKEYS FILES=F1,FIELDS=(1,4,A)
JOINKEYS FILES=F2,FIELDS=(1,4,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,80,?)
OPTION COPY
OUTFIL FNAMES=OFILE1,INCLUDE=(81,1,CH,EQ,C'B'),
BUILD=(1,80)
OUTFIL FNAMES=OFILE2,INCLUDE=(81,1,CH,EQ,C'1'),
BUILD=(1,80)
OUTFIL FNAMES=OFILE3,INCLUDE=(81,1,CH,EQ,C'2'),
BUILD=(1,80)

Note:
B=both -> In my case I need to write matching/unmatching records into separate files

Arun Raj · Posted: Tue Feb 07, 2017 3:11 am

scdinesh,

Since you need to write the entire record area from both the inputs, your REFORMAT should include both F1 and F2 fields. Something like,

scdinesh · Posted: Tue Feb 07, 2017 9:09 pm

Thanks Arun for the help!!!

"Your OFILE1 (matched records from input1) and OFILE2 (matched records from input2) should both INCLUDE 'B' records, but write only F1 and F2 records respectively into each of those outputs"

In my case I want to write only F1 records into OFILE1 upon condition , how does it know it should write only F1 records? (as we have mentioned both F1 and F2 in 'Reformat fields'). Please help

Arun Raj · Posted: Tue Feb 07, 2017 9:33 pm

scdinesh · Posted: Wed Feb 08, 2017 8:47 pm

Thanks Sergeyken,Bill and Arun

scdinesh · Posted: Wed Feb 08, 2017 11:30 pm

Hi,
Please see if there is anything wrong in the below sort upon my above requirement? am seeing empty records in all 4 output files.

JOINKEYS F1=CONCUR,FIELDS=(1,4,A)
JOINKEYS F2=LOOKUP,FIELDS=(1,4,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,80,F2:1,80,?)
SORT FIELDS=COPY
OUTFIL FILES=01,INCLUDE=(81,1,CH,EQ,C'B'),BUILD=(1,80)
OUTFIL FILES=02,INCLUDE=(81,1,CH,EQ,C'B'),BUILD=(1,80)
OUTFIL FILES=03,INCLUDE=(81,1,CH,EQ,C'1'),BUILD=(1,80)
OUTFIL FILES=04,INCLUDE=(81,1,CH,EQ,C'2'),BUILD=(1,80

Thanks

Arun Raj · Posted: Wed Feb 08, 2017 11:32 pm

Your match marker position is 161 and NOT 81.

Plus you need to fix your BUILDs as suggested earlier.

John Del · New User Joined: 27 Apr 2012 Posts: 42 Location: NY

scdinesh -

I would suggest that you first just write the reformatted output to one dataset, so that you can see how the join operation is outputting the joined data in relation to the match marker value(s). That might help you to understand how to write your INCLUDE conditions and BUILD parameters for the OUTFIL files.

/*