Changing data when sorting two files



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Changing data when sorting two files

Postby Puffe » Tue Oct 25, 2011 5:49 pm

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
Puffe
 
Posts: 21
Joined: Mon May 02, 2011 1:41 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Changing data when sorting two files

Postby Frank Yaeger » Wed Oct 26, 2011 1:31 am

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.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Changing data when sorting two files

Postby Puffe » Wed Oct 26, 2011 10:13 am

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.
Puffe
 
Posts: 21
Joined: Mon May 02, 2011 1:41 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Changing data when sorting two files

Postby BillyBoyo » Wed Oct 26, 2011 11:55 am

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Changing data when sorting two files

Postby enrico-sorichetti » Wed Oct 26, 2011 12:43 pm

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
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: Changing data when sorting two files

Postby Frank Yaeger » Wed Oct 26, 2011 10:52 pm

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".
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Changing data when sorting two files

Postby Puffe » Fri Oct 28, 2011 11:26 am

That's exactly what I wanted Frank, so you got it right. Thanks a lot!!!!
Puffe
 
Posts: 21
Joined: Mon May 02, 2011 1:41 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post