Match key from two input files and format the output file

vijaypratapnayak · New User Joined: 04 Oct 2005 Posts: 7

I have below requirement, which I want to fulfil using SORT.

I have two input files which has a field (7,5 i.e 5 bytes from position 7) which makes it unique. I want to match the key from 1st file to the key on the 2nd file. If key matches, I want to filter some fields from the 1st and 2nd file and write the new output file. The example in given below.

1st File:
----+----1----+----2----+----3
A0002,B0002,C0002,D0002,E0002
A0003,B0003,C0003,D0003,E0003
A0005,B0005,C0005,D0005,E0005
A0007,B0007,C0007,D0007,E0007

2nd File:
----+----1----+----2----+----3
F0001,B0001,G0001,H0001,I0001
F0002,B0002,G0002,H0002,I0002 Has matching key as 1st file.
F0003,B0003,G0003,H0003,I0003 Has matching key as 1st file.
F0004,B0004,G0004,H0004,I0004
F0005,B0005,G0005,H0005,I0005 Has matching key as 1st file.
F0006,B0006,G0006,H0006,I0006
F0007,B0007,G0007,H0007,I0007 Has matching key as 1st file.
F0008,B0008,G0008,H0008,I0008
F0009,B0009,G0009,H0009,I0009

Output file:
B0002,D0002,E0002,G0001,H0001 Fields are pulled from 2nd file.
B0003,D0003,E0003,G0003,H0003 Field are pulled from 2nd file.
B0005,D0005,E0005,G0005,H0005 Field are pulled from 2nd file.
B0007,D0007,E0007,G0007,H0007 Field are pulled from 2nd file.

Please let me know how can we acheive this and please provide me with sample sort card if have any. Would appriciate your help.

William Thompson · Posted: Thu Apr 19, 2007 2:08 am

Have you looked at any DFSORT or Syncsort manuals for direction?

PeD · Posted: Thu Apr 19, 2007 2:58 am

vijaypratapnayak · New User Joined: 04 Oct 2005 Posts: 7

Yeah there was mistake in the output declaration. Thanks for you help. I had one document on DFSORT which i got it from IBM site but not finding it very useful to match the current criteria.

=============================================
I have below requirement, which I want to fulfil using SORT.

I have two input files which has a field (7,5 i.e 5 bytes from position 7) which makes it unique. I want to match the key from 1st file to the key on the 2nd file. If key matches, I want to filter some fields from the 1st and 2nd file and write the new output file. The example in given below.

1st File:
----+----1----+----2----+----3
A0002,B0002,C0002,D0002,E0002
A0003,B0003,C0003,D0003,E0003
A0005,B0005,C0005,D0005,E0005
A0007,B0007,C0007,D0007,E0007

2nd File:
----+----1----+----2----+----3
F0001,B0001,G0001,H0001,I0001
F0002,B0002,G0002,H0002,I0002 Has matching key as 1st file.
F0003,B0003,G0003,H0003,I0003 Has matching key as 1st file.
F0004,B0004,G0004,H0004,I0004
F0005,B0005,G0005,H0005,I0005 Has matching key as 1st file.
F0006,B0006,G0006,H0006,I0006
F0007,B0007,G0007,H0007,I0007 Has matching key as 1st file.
F0008,B0008,G0008,H0008,I0008
F0009,B0009,G0009,H0009,I0009

Output file:
B0002,D0002,E0002,G0002,H0002
B0002,D0002,E0002 Pulled from First File
G0002,H0002 Pulled from Second File
Simlar Output records..
B0003,D0003,E0003,G0003,H0003 Field are pulled from 2nd file.
B0005,D0005,E0005,G0005,H0005 Field are pulled from 2nd file.
B0007,D0007,E0007,G0007,H0007 Field are pulled from 2nd file.

Please let me know how can we acheive this and please provide me with sample sort card if have any. Would appriciate your help.

Frank Yaeger · Posted: Thu Apr 19, 2007 5:16 am

Here's a DFSORT/ICETOOL job that will do what you asked for:

vijaypratapnayak · New User Joined: 04 Oct 2005 Posts: 7

Please accept my sincere thanks to all of you for helping me out. The solution given by Frank Yaeger has worked and it is pretty fast. I will be keep checking this site for someone to whom i can help next time. Thanks once again.

Vijay Nayak

vijaypratapnayak · New User Joined: 04 Oct 2005 Posts: 7

Hi, I got some change in my requirment. The input file can have a duplicates in the key field. The below code SORT card will not work with duplicates. Please see the example.

A0002,B0002,C0002,D0002,E0002
A0003,B0003,C0003,D0003,E0003
A0008,B0003,C0008,D0008,E0008
A0005,B0005,C0005,D0005,E0005
A0007,B0007,C0007,D0007,E0007

The output should also have two records for the key field B0003 something like the one given below.

B0002,D0002,E0002,G0002,H0002
B0003,D0003,E0003,G0003,H0003
B0003,D0008,E0008,G0003,H0003
B0005,D0005,E0005,G0005,H0005
B0007,D0007,E0007,G0007,H0007

Frank Yaeger · Posted: Fri Apr 20, 2007 12:55 am

vijaypratapnayak · New User Joined: 04 Oct 2005 Posts: 7

No Frank Second fill not have duplicate. This was a requirement change and was not know to me before that the first input file may have duplicates in it. Sorry for the inconvenience caused.

Frank Yaeger · Posted: Fri Apr 20, 2007 1:27 am

Here's a revised DFSORT/ICETOOL job that will handle the duplicates:

vijaypratapnayak · New User Joined: 04 Oct 2005 Posts: 7

Great!!! it worked Yaeger....

vijaypratapnayak · New User Joined: 04 Oct 2005 Posts: 7

Could you please let me confirm my understanding. This could be a basic question. This is my first experience using ICETOOL.

SPLICE FROM(T1) TO(OUT) ON(1,5,CH) -
WITHALL WITH(7,11)

The keyword WITHALL WITH(7,11) mean allow mapping of all the duplicate on the file-2?

What does SPLICE FROM(T1) TO(OUT) ON(1,5,CH) - statement implicates?

William Thompson · Posted: Fri Apr 20, 2007 4:15 am

Frank Yaeger · Posted: Fri Apr 20, 2007 4:37 am

Yes, and I'd also say:

For complete details on the SPLICE operator of DFSORT's ICETOOL, see:

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.13?DT=20060615185603

and for some examples of using SPLICE for matching see the "Join fields from two files on a key" and "Join fields from two files record-by-record" Smart DFSORT Tricks at:

www.ibm.com/servers/storage/support/software/sort/mvs/tricks/

achittu · New User Joined: 26 Feb 2007 Posts: 14 Location: chennai

Frank,

I have a similar requirement but I may have unmatched records in the File 1 . The above Sort will get all the matched records. What if I also want to write to output all those records in File 1 which do not have a match in File 2.

How do I achieve this ?

Frank Yaeger · Posted: Thu Apr 26, 2007 9:30 pm

achittu,

Please start a new Topic.

In your initial post, show an example of the records in each input file and what you expect for the output records. If input file1 can have duplicates within it, show that in the example. If input file2 can have duplicates within it, show that in the example. Also, give the RECFM and LRECL of the input files, and the starting position, length and format of the relevant fields in each input file.