I have a typical requirement. I have to compare two files File A & File B.Both the files are having lenght as 125.Only when the amount value is different for same key, then the output file should be written from File B. But before writing the record to output file, the amount value need to be subtracted and final value should be written to output file.
File A -
A123 1000.00
B123 1500.00
C123 4000.00
File B -
A123 500.00
B123 2500.00
C123 6000.00
O/P file -
A123 -500.00
B123 1000.00
C123 2000.00
I have used the following SORT card, where I am using first 4 characters as the key. I am trying to subtract the value of amount field which is at 75th position in my file. But it doesn't give me the desired value.
2) It would be nice to use the utility statements in the order they are really executed. It is not "programming language" with sequential statements; it's the description of required task.
RTFM!
3) The two of your requirements are contrary with each other:
- Only when the amount value is different for same key, then the output file should be written from File B.
and
- But before writing the record to output file, the amount value need to be subtracted and final value should be written to output file.
Actually there are few other fields as well, which may differ in both the files. So if it's different then we need to consider it from File B only. And then we need to subtract the values of amount and write the subtracted value along with record details of File B to output.
Forgot to mention that the joinkeys in Sort card have the amount field as well along with few other fields. So it's a combination that's checked. If the combined value is different then consider the record from File B. That's why I have mentioned 'JOIN UNPAIRED F2 ONLY'.
Forgot to mention that the joinkeys in Sort card have the amount field as well along with few other fields. So it's a combination that's checked. If the combined value is different then consider the record from File B. That's why I have mentioned 'JOIN UNPAIRED F2 ONLY'.
You do not understand the meaning of this statement - 'JOIN UNPAIRED F2 ONLY'
It does the operation ALMOST OPPOSITE to what you described.
I don't want to give you ready-to-use solution, I prefer people to understand themselves what they are really doing, but not to copy-paste the code from others.
That's why - RTFM.
I do understand the meaning of ' JOIN UNPAIRED' and I don't want ready to use solution. Only thing I want is to know, whether it's possible to use sort to compare and subtract at the same time. As I tried and tested everything possible by me, and as I could not find the result, that's why I came to this forum to check and see if someone can help me. And my intention was never to get ready made solution. So please guide me if you can, rather than writing negatively. Otherwise I am others help in this regard.
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
ameetmund wrote:
Forgot to mention that the joinkeys in Sort card have the amount field as well along with few other fields. So it's a combination that's checked
ameetmund,
The idea here is to include only the actual key fields in your JOINKEYS (exclude the amount field), and then extract ONLY matching records from both the files, then work with the amount comparison later on.
Instead of JOIN UNPAIRED,F2,ONLY you probably need to remove the JOIN statement altogether which would give you matching key-records from both the input files.
JOIN UNPAIRED,F2,ONLY - would write ONLY non-matching records from F2.
Then use an OUTREC IFTHEN WHEN condition to check if the amount fields from both the files are different, then OVERLAY the calculated difference amount to the output record.
Something similar to this, if I modify the control statements from your original post:
And remember this would NOT write non-matching records from either of the inputs, if you need non matching records too (I don't see that mentioned in your posts), you would have to modify this as per your requirements. Also I have assumed your amount field to be PD as shown in your control statements.