Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Compare two files and subtract values

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Compare two files and subtract values
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

Active User


Joined: 29 Apr 2008
Posts: 189
Location: Maryland

PostPosted: Fri Mar 31, 2017 4:36 pm    Post subject:
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    Post subject: Reply to: Compare two files and subtract values
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    Post subject: Reply to: Compare two files and subtract values
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

Active User


Joined: 29 Apr 2008
Posts: 189
Location: Maryland

PostPosted: Fri Mar 31, 2017 5:12 pm    Post subject: Re: Reply to: Compare two files and subtract values
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    Post subject: Reply to: Compare two files and subtract values
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: 2285
Location: @my desk

PostPosted: Fri Mar 31, 2017 5:55 pm    Post subject:
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    Post subject: Reply to: Compare two files and subtract values
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Subtract the ZD fields to provide neg... Balaryan DFSORT/ICETOOL 4 Wed Oct 11, 2017 10:51 pm
No new posts Extend the decimal values in DIVISION Balaryan DFSORT/ICETOOL 3 Thu Oct 05, 2017 4:05 pm
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts File Aid tool to compare numeric data balaji81_k Compuware & Other Tools 2 Tue Sep 26, 2017 3:35 am
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us