Page 1 of 1

Changing data when sorting two files

PostPosted: Tue Oct 25, 2011 5:49 pm
by Puffe
Hi,

I have two files that are FB but different length.
File B has more records than file A.

File A (lrecl=19, key in position 6-11)
MS001196408208888AA
MS002196408221111BB
MS003196408222222BB
MS004196409239999BB

File B (lrecl=22, key in position 1-6)
1964082100112010-10-10
1964082100222010-11-01
1964082211222009-01-01
1964082222332009-02-01
1964092433442010-01-01

The join should be between the keys above.
I want all the records in file A (position 6-17) to be changed with the value in file B (position 1-12) if the key matches.

The result I want in file C is
MS001196408210011AA
MS002196408210022BB
MS003196408221122BB
MS004196409243344BB

Regards,

Mikael

Re: Changing data when sorting two files

PostPosted: Wed Oct 26, 2011 1:31 am
by Frank Yaeger
It's not clear what you want to do.

According to your description, the keys in file1 are:

196408
196408
196408
196409

and the keys in file2 are:

196408
196408
196408
196408
196409

so the first 3 keys in file1 match the first 4 keys in file2. Is that what you really have/want?

If so, do you want to replace based on the sequence number of duplicate keys, e.g.
file1 key1 record01 with file2 key1 record01
file1 key1 record02 with file2 key1 record02
...

If not, what do you want exactly?

Please state more clearly what it is you want to do.

Re: Changing data when sorting two files

PostPosted: Wed Oct 26, 2011 10:13 am
by Puffe
What I want is to change all records in File A with records from File B but only partly (position 6-17).

So record no 1 in File A
MS001196408208888AA
matches File B record no 1 with key 196408 and therefore the new record in File C should be (pos 1-5 from File A, pos 6-17 from File B, pos 18-19 from File A)
MS001196408210011AA

And for record no 2 in File A
MS002196408221111BB
matches File B record no 2 with key 196408 and therefore the new record in File C should be (pos 1-5 from File A, pos 6-17 from File B, pos 18-19 from File A)
MS002196408210022BB

And so on until all records in File A is changed.

Yes, File B have more keys (196408) than File A.

So every record in file A should get a unique value (position 6-17) from File B.

I hope I made it more clearly to You.

Re: Changing data when sorting two files

PostPosted: Wed Oct 26, 2011 11:55 am
by BillyBoyo
so the first 3 keys in file1 match the first 4 keys in file2. Is that what you really have/want?

If so, do you want to replace based on the sequence number of duplicate keys, e.g.
file1 key1 record01 with file2 key1 record01
file1 key1 record02 with file2 key1 record02


I still don't think you've have clarified this.

Re: Changing data when sorting two files

PostPosted: Wed Oct 26, 2011 12:43 pm
by enrico-sorichetti
lets reword the logic ...
file a
key0-some-value1-rest-of-record-for-file-a
key0-some-value2-rest-of-record-for-file-a

key1-some-value1-rest-of-record-for-file-a
key1-some-value2-rest-of-record-for-file-a
key1-some-value3-rest-of-record-for-file-a

key2-some-value1-rest-of-record-for-file-a
key2-some-value2-rest-of-record-for-file-a
key2-some-value3-rest-of-record-for-file-a

file b
key0-other-value1-rest-of-record-for-file-a
key0-other-value2-rest-of-record-for-file-a

key1-other-value1-rest-of-record-for-file-b
key1-other-value2-rest-of-record-for-file-b
key1-other-value3-rest-of-record-for-file-b
key1-other-value4-rest-of-record-for-file-b

key2-other-value4-rest-of-record-for-file-b
key2-other-value4-rest-of-record-for-file-b

output ( logic match)
key0-other-value1-rest-of-record-for-file-a
key0-other-value2-rest-of-record-for-file-a

key1 ????
key2 ????


how do You want to match things when the number of records with the same key is different ?
is it so difficult to answer ?
post the expected result for key1 and key2

Re: Changing data when sorting two files

PostPosted: Wed Oct 26, 2011 10:52 pm
by Frank Yaeger
do you want to replace based on the sequence number of duplicate keys


Mikael,

I think you're saying the answer to my question is yes. If so, then you can use a DFSORT JOINKEYS job like the following to do what you want:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/19)
//IN2 DD DSN=... input file2 (FB/22)
//SORTOUT DD DSN=...  output file (FB/19)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(6,6,A,20,8,A)
  JOINKEYS F2=IN2,FIELDS=(1,6,A,23,8,A)
  REFORMAT FIELDS=(F1:1,5,F2:1,12,F1:18,2)
  OPTION COPY
/*
//JNF1CNTL DD *
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(6,6),PUSH=(20:SEQ=8))
/*
//JNF2CNTL DD *
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,6),PUSH=(23:SEQ=8))
/*


For your input example, SORTOUT would have:

MS001196408210011AA                   
MS002196408210022BB                   
MS003196408221122BB                   
MS004196409243344BB                   


If there are other cases for which this doesn't do what you want, you need to show me an example of input and expected output for those cases and explain the "rules".

Re: Changing data when sorting two files

PostPosted: Fri Oct 28, 2011 11:26 am
by Puffe
That's exactly what I wanted Frank, so you got it right. Thanks a lot!!!!