joinkeys with no repeated records

widia · New User Joined: 04 Mar 2009 Posts: 6 Location: Chennai

Hi All,
I am new to this forum.I need to solve a issue. I have compared two files and joined them using JOINKEYS. I am facing a issue regarding this. I ve joined from column 5 to column 10.

consider 1st file
ddd 123456 name1 234
ddd 123456 name1 567
kkk 789444 name2 791

2nd file
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr

Desird output file
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr

but i am gettin output file as
ddd 123456 kkkk
ddd 123456 ssss
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr

My code is
JOINKEYS FILES=F1,FIELDS=(5,6,A)
JOINKEYS FILES=F2,FIELDS=(5,6,A)
REFORMAT FIELDS=(F1:1,11,F2:12,4)
SORT FIELDS=COPY

It is repeated four times i need to get only twice.Please help me out.

Arun Raj · Posted: Sat Apr 11, 2009 3:53 pm

widia,

Hello and welcome to the forums.

The JOIN operation does a cartesian product and since you have duplicate keys in both the files, you will get the records repeated. You might need to remove duplicates from either of your input files before the JOIN operation.

From the sample data posted above, Posn 1-11 seem to be the same in both the input files. Why would you need the first 11 positions from file-1 and NOT from file-2? You need to provide a little more realistic data which covers all the possible input scenarios.

widia · New User Joined: 04 Mar 2009 Posts: 6 Location: Chennai

Hi arcvns,

As per my requirement, the input file can have duplicate keys. The Second input file is processed by first input file and i get additional information. So i append certain fields from first file and additional information from second file.

consider 1st file
ddd 123456 name1 234
ddd 123456 name1 567
kkk 789444 name2 791

2nd file
ddd 123456 kkkk
ddd 123456 ssss
kkk 789444 rrrr

Here 234 and 567 makes difference in first file. They are processed in second file and i get 'KKKK' for ddd 123456 name1 234 and 'ssss' for ddd 123456 name1 567 .So in my requirement keys can repeat but i need to join without repetation in output file.

Thanks in advance.

sril.krishy · Posted: Tue Apr 14, 2009 4:03 pm

widia,
Either you can sort the i/p file without duplicates before join or sort the output file generated from the join to avoid the duplicates.

Thanks
Krishy

Arun Raj · Posted: Tue Apr 14, 2009 6:04 pm

widia,

Will there be non-matching keys in file-1 or file-2? Your sample data has only matching records and it makes difficult to make out how your actual data would be. Also in your example, the 2nd input file and the output file are one and the same.

widia · New User Joined: 04 Mar 2009 Posts: 6 Location: Chennai

There will not be any non-matching keys as my first input file is processed to get second input file. The desired output file is not exactly same as second input file.It is concatenation of first and second input file.

dick scherrer · Posted: Thu Apr 16, 2009 8:51 pm

Hello,

If the second file is data pulled from the first file, why is it needed?

If you post some more realistic and more complete "input" data (rather than alphabet soup) and post what you want as the actual output from the sample input, someone may have a suggestion.

widia · New User Joined: 04 Mar 2009 Posts: 6 Location: Chennai

Hi Dick,
To be more precise i ll give you the sample data,
consider file1
Rec 1: ddd 089376578 widia 574royal-st 234new-st 04/17/09
Rec 2: ddd 089376578 widia 574royal-st 234new-st 04/18/09
Rec 3: ddd 067457868 Neha 234old-st 768queen-st 04/16/09

File2
Rec 1: ddd 089376578 widia 574royal-st 234new-st reason:notmatched
Rec 2: ddd 089376578 widia 574royal-st 234new-st reason:notmatched
Rec 3: ddd 067457868 Neha 234old-st 768queen-st reason:updated

Output file
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 067457868 Neha 234old-st 768queen-st reason:updated

So the first file is processed to get reason in second file. Here last field date is different for the first input file.Joinkeys are perfect when the record is present once. But when it is repeated twice, i get four records in desired output file.Sorry for the inconvenience.

dick scherrer · Posted: Sat Apr 18, 2009 1:12 am

Hello,

I still do not understand why file 2 is created. . .

Why can file 1 not be read and the output file written as needed with the appropriate reason?

Alissa Margulies · Posted: Mon Apr 20, 2009 10:08 pm

Widia,

I'm with Dick on this one. How is File 2 any different than your desired output from the JOIN processing? Isn't File 2 exactly what you are already looking for? If not, then you need to provide additional details and a better example of your desired output.

widia · New User Joined: 04 Mar 2009 Posts: 6 Location: Chennai

Hi,

I can give u additiona info for input like this, hope this helps
File 1:
Rec 1: ddd 089376578 widia 574royal-st 234new-st 04/17/09
Rec 2: ddd 089376578 widia 574royal-st 234new-st 04/18/09
Rec 3: ddd 067457868 Neha 234old-st 768queen-st 04/16/09

File 2:
Rec 1: ddd 089376578 Error : 0000 reason:notmatched
Rec 2: ddd 089376578 Error : 0000 reason:notmatched
Rec 3: ddd 067457868 Error : 0000 reason:updated

Output file:
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 089376578 widia 574royal-st 234new-st reason:notmatched
ddd 067457868 Neha 234old-st 768queen-st reason:updated

I need to write only reasons from second file.Hope now the issue can be solved.

Alissa Margulies · Posted: Wed Apr 22, 2009 8:26 pm

Here are 2 SyncSort for z/OS jobs that will do what you asked:

widia · New User Joined: 04 Mar 2009 Posts: 6 Location: Chennai

Thanks Alissa Margulies..

it is working fine. Thanks a lot..