IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Compare two files and subtract values


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ameetmund

New User


Joined: 02 Jun 2006
Posts: 11

PostPosted: Fri Mar 31, 2017 3:35 pm
Reply with quote

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.

//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS F1=IN1,FIELDS=(1,4,A)
JOINKEYS F2=IN2,FIELDS=(1,4,A)
JOIN UNPAIRED,F2,ONLY
REFORMAT FIELDS=(F2:1,125,F1:1,125)
OUTREC FIELDS=(1:1,74,
75:200,8,PD,SUB,75,8,PD,TO=PD,LENGTH=8,
83,43)

I referred IBM manual and other links to get information about it, but could not get the right information. Please suggest any idea to resolve this.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Fri Mar 31, 2017 4:36 pm
Reply with quote

1) Your statement
Code:
JOIN UNPAIRED,F2,ONLY
is contrary with your requirement.
RTFM!

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.
Back to top
View user's profile Send private message
ameetmund

New User


Joined: 02 Jun 2006
Posts: 11

PostPosted: Fri Mar 31, 2017 4:48 pm
Reply with quote

Hi Sergyken,

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.

I hope it's clear now.
Back to top
View user's profile Send private message
ameetmund

New User


Joined: 02 Jun 2006
Posts: 11

PostPosted: Fri Mar 31, 2017 4:58 pm
Reply with quote

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'.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Fri Mar 31, 2017 5:12 pm
Reply with quote

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. 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.
Back to top
View user's profile Send private message
ameetmund

New User


Joined: 02 Jun 2006
Posts: 11

PostPosted: Fri Mar 31, 2017 5:40 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Fri Mar 31, 2017 5:55 pm
Reply with quote

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:
Code:
//SYSIN DD *
 SORT FIELDS=COPY
 JOINKEYS F1=IN1,FIELDS=(1,4,A)
 JOINKEYS F2=IN2,FIELDS=(1,4,A)
 REFORMAT FIELDS=(F2:1,125,F1:75,8)
 OUTREC IFOUTLEN=125,
        IFTHEN=(WHEN=(75,8,PD,NE,126,8,PD),
        OVERLAY=(75:126,8,PD,SUB,75,8,PD,TO=PD,LENGTH=8))
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.
Back to top
View user's profile Send private message
ameetmund

New User


Joined: 02 Jun 2006
Posts: 11

PostPosted: Fri Mar 31, 2017 7:32 pm
Reply with quote

Arun Raj,

Thank you so much. Your suggestion helped me to run my job. It's working for me now. Thanks again :-)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
Search our Forums:

Back to Top