Query: My ask is to have an output file where Amounts from both files are added based on the key in positions 1 to 15 (name) & 28 to 30 (Currency) and the output file should be in the same format as the File 1.
Note:
- The File 1 is the driver file.
- File 2 may not contain all names from File 1.
- When above such scenario occurs, the record from File 1 should be written to output as it is. (i.e. adding a zero to the amount field).
I think I should use SORT JOIN to match the two files and SUM FIELDS for adding the Amounts but not sure how.
I tried the below control card to get the addition part.
Now the problem is that the output I receive is in ZD format and the file is longer than expected.
Expected output was 30 bytes long same as input file and with the Amount field containing the addition of the Amount fields from File 1 and File 2 for the matched records only.
For unmatched records, the record from the input File 1 is supposed to be as it is copied to output file.
But as you can see below, the output file created is 38 bytes long with the amount field taking up the additional 8 bytes.
In above, I used SORTOF01 to get my intended result for matched records with added amount fields.
But I had stated in my first post, I need the unmatched records from the File 1 (driver file) as well.
So, I am using SORTOF02 to get those unmatched records from File 1.
In the next step, I will merge the file from SORTOF01 and SORTOF02 to get the final required file.
I have multiple PD fields in my actual file, for which I will repeat the same logic for other fields in SORTOF01.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Well, I'd recommend using the length. It is more typing for now, but it'll save lots of confusion in the long run (people have to look up in a manual how long the field will be while trying to check that the data is in the correct position, a silly thing to leave someone else to do).
Are you using SyncSORT? If not, use the "match marker", ?, for the REFORMAT record, and from F2 only include the data that you need from F2.
Test the match-marker position, and do the calculation only on a match (marker equal to B). Use IFOUTLEN=30 to drop off the excess bytes (match-marker and amount).
If you don't have access to the match-marker, you can work out whether you have a match or not, using a test for the "FILL" character that you have provided.
No need for two OUTFILs. No need for another step.
Also, if you use FILES=01, you don't need FNAMES=SORTOF01. Alternatively, specify a more description name for FNAMES.
Note that the F1,F2 are a "comment" in the JOIN you have shown, plus you get F1 and F2 mismatches anyway from UNPAIRED. F1 we know what you want with mismatches, what about F2 mismatches?