Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need to match the records from first file to second file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
sasikumar1984

Active User


Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India

PostPosted: Sun Jul 08, 2007 11:04 am    Post subject: Need to match the records from first file to second file
Reply with quote

Hi, can anyone help me to slove the following problem pls.

I have two files. i need to match the records from first file to second file using a nine (first 7 digits are unique Account num and next two digits are occurrence of that account num like 01,02,03 etc becos i may get many records for same account number) digit value as a key contains in both the files. As soon as i find match in second file i want make that records as read. Becos second file may or may not contains all the records that are in first file for same account num.(For ex: if first file contains 10 records for same account num, second file may contains 10 or less than 10). But all these 10 records in first file and second file looks same. Can anyone tell me how to proceed on this???
Back to top
View user's profile Send private message

Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Sun Jul 08, 2007 6:58 pm    Post subject:
Reply with quote

Match file logic can be very tricky......I suggest you sit down and draw a logic flow (picture). When you first come into the program, read both files. Never assume that it will be a one to many match. You could end up with a situation where file-1 has a record and file-2 does not. Maybe file-2 has a record and file-1 does not. In each situation, you will need logic to determine IF either file needs to be read. Also, take into consideration end-of-file conditions on each file when there are still records on either file. You will need to do less than and greater than logic. I am assuming that you will be writing a 3rd file - perhaps a report?
Back to top
View user's profile Send private message
sasikumar1984

Active User


Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India

PostPosted: Sun Jul 08, 2007 10:50 pm    Post subject: Re: Need to match the records from first file to second file
Reply with quote

yes, i am going to create two neaw file while matching file-1 and file-2. one file contains matchin records and other contains unmatched records.i have to match record from file-1 to file-2. Can i load file-2 in arrays format using COBOL??? or we have any other method????? can anyone give me good sample program for COBOL using files as arrays. i badly need this asap.. friends help me...
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Sun Jul 08, 2007 11:46 pm    Post subject:
Reply with quote

How large would you make that array? icon_lol.gif

Sit down and draw a picture. When reading sequential records into a cobol program, you have no idea how much data is coming in on those filles. The other thing you must insist on is that both files are sorted in the same sequence. It has been years, but I will look for a program. I have thown most of them out, but there may me one somewhere around. If, however, you really want to learn file matching logic, draw some logic flows.
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Sun Jul 08, 2007 11:48 pm    Post subject:
Reply with quote

OH....one more thing to consider is a flag on the new files that indicate where the match was found or not found - which file did not have a matching record or records.
Back to top
View user's profile Send private message
dappyin

New User


Joined: 23 Dec 2006
Posts: 2
Location: Gurgaon

PostPosted: Mon Jul 09, 2007 1:13 am    Post subject: Re: Need to match the records from first file to second file
Reply with quote

Hi, Form your problem I am assuming that both of the files are already sorted and you just want to compare the records on these files and put the records into corresponding 2 new files. 1) for Matched records 2) For unmatched records.

Code the paras as per which whenever end of file is read you read High Value as a record of that file. This applies for both of the files.

As files are sorted so pick up the 1st record of each and start the compare function. 3 possible results are as below

1. If Record (a) < Record (b)
put Record (a) into Unmatched file and read a new record from File A. and start compare again.

2. If Record (a) > Record (b)
put Record (b) into Unmatched file and read a new record from File B. and start compare again.

3. If Record (a) = Record (b)
Make sure than one of these records <> High Value (to make sure we haven't reached the end of file).. If it's equal, its end of program Else put the record into Matched file and fetch a new record of both the files A and B.


I hope with this flow, you will get the desired output.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jul 09, 2007 3:17 am    Post subject:
Reply with quote

Hello,

You will probably not want to use one or more arrays to accomplish a 2-file match/merge.

While many places/people make this a difficult task, it is not necessary that this be diffieult. . .

Due to other topics on this subject, i've nearly completed a set of sample code to post as a "sticky".

This
Quote:
3. If Record (a) = Record (b)
Make sure than one of these records <> High Value (to make sure we haven't reached the end of file).. If it's equal, its end of program Else put the record into Matched file and fetch a new record of both the files A and B.
can cause problems in some cases. If one file reached eof before the other, the only thing that should happen is that the "eof"ed file sould not be read any more. The "other" file needs to be processed thru eof. The process is only done when both files reach eof.

A single set of IFs that determine the relationship of the 2 records currently in process will determine which set of code should be executed. They are:
1. FileA "match key" = FileB "march key"
2. FileA "match key" > FileB "march key"
3. FileA "match key" < FileB "march key"
Reaching this point is cause to abend - the code cannot get here. If the code does get here it is a programming error and the code should stop immediately.

There needs to be one and only one read of each input file. Do not keep adding reads to "balance" the process - one read of each is much simpler to code and far easier to maintain.

Sasikumar - If you are really stuck, and are willing to try a pre-release copy of the generic match/merge code, send me a PM and i'll try to send you enough to get you moving forward in the morning when i have access to the server where it is stored. That server is on an in-house lan and does not support remote connections.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Jul 09, 2007 7:25 am    Post subject:
Reply with quote

I see the need for a program when you are actually doing processing logic or generating a report, not just splitting a file, but...

<vent>
Since this has been one of the most common uses of a batch COBOL program for almost forever, (and it isn't usually too hard) why hasn't something been produced or a standard module been added that can handle this type of logic? (Thanks Dick for providing one for people to reference)

I had a similar problem a few months back. I had 2 files I needed to compare and put the unmatched records to an output file. I had no control over the input files, and I new they were unsorted. I was told, sort them then write some COBOL or EZTrieve to do the compare.

I just ended up using a sort to take care of it all in one step.
</vent>
Back to top
View user's profile Send private message
sasikumar1984

Active User


Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India

PostPosted: Mon Jul 09, 2007 7:55 am    Post subject: Re: Need to match the records from first file to second file
Reply with quote

Hi,

Many thanks to all for the response. Actually i made bit confusion here. Pls find the below steps

how i wanted to do.

File - 1

Acct-Num Trans Chk-Amt
1817415 RET 2000
1817415 RET 2000
1817415 WDL 1000
1817415 RET 2000
1817415 RET 2000


FILE-2 (This is how my File-2 which i get from 3rd party)

Acct-Num Trans Chk-Amt

1817415 RET 2000
1817415 RET 2000
1817415 WDL 1000
1817415 RET 2000

Step 1: I need to Read the File-1 and fetch Acct-Num from File-1 and find the same Acct-Num in File-2.

Step 2: As soon as i find Acct-Num in File-2, i have to validate 'Trans' and 'Chk-Amt'. If both are equal then i want this record move to 'Matching-File'(its a new file). If 'Trans' and 'Chk-Amt' dont match for the same Acct-Num then i want check if any other record is there in
File-2 with same Acct-Num. If its there then again i will validate Trans' and Chk-Amt',this i need to do until it finds the Acct-Num and the values of 'Trans' and 'Chk-Amt are equal. If i dont find exact match in File-2 then i want this record moved to 'Unmatched-File'(its a new file).

Step 3: If i find exact match (Same 'Acct-Num','Trans' and 'Chk-Amt') then i want make that

record is already matched by moving a Flag in File-2. This is becos File-1 contains duplicate

records which may or may not available in File-2.



I will sort both the file using 'Act-Num'. Why i wanted to move flag in File-2 is , From the

example files which i gave above, File-1 contains four out of five records with same

'Acct-Num','Trans' and 'Chk-Amt'. But File-2 contains only three with same Acct-Num,'Trans' and

Chk-Amt'. So if i dont move flags to File-2 that i already read these records then when fifth

record comes from File-1, it will match with first record of File-2, but this record i already

matched with First record of File-1. So i wanted this record move to 'Unmatched-File'.(FYI

sometimes in File-2 i get all the records that File-1 contains)

Please help me, i am somewhat fresher in MF. so i couldnt able to handle this and daily my

Manager is scolding me...i badly need help from u all....
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jul 09, 2007 8:44 am    Post subject:
Reply with quote

Hello,

Why is the WDL shown "in the middle"? How are the 2 files sequenced?

What is the maximum number of records for a single Acct-Num in either file?

Please post a bit more input data with a few acct#s. From the expanded input, post what you want for output.

Again, from what i've seen of your requirement so far, you do not want "flags in file2".

Once we see what your output requirement is for the more complete input, we will be better able to offer suggestions. The more complete your example input/output, the faster you will reach a workable solutoin.
Back to top
View user's profile Send private message
sasikumar1984

Active User


Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India

PostPosted: Mon Jul 09, 2007 2:52 pm    Post subject:
Reply with quote

Hi,

My File-1 is a length of 2984, but i use above mentioned values(variable only). File-2 lenght is 500 where the above mentioned fileds only matched with that of File-1. thats what i gave examples like that. 'Trans' means Transactions. SO Same account holder do different kinds of transactions.
So i get same 'Acct-Num' with different 'Trans' and 'Chk-Amt'. SO for every read in File-1, i have to search the 'Acct-Num' once i get i have to validate other two values. If other two values also matched then i want this record moved to 'Matched' File. If i dont find 'Trans' and 'Chk-Amt' for all the occurrence of the same 'Acct-Num' in File-2, then i wanted this record moved to 'Unmatched' File. Here one more point, Same 'Acct-Num' can perform more than one 'RET' transactions with same amount. In that case i get two records in File-1. File-2 how i get is once it is processed only. So if any one of 'RET' is not processed then i dont get it File-2. But
File-1 contains two of 'RET's so when i get second RET from File-1 how i come to know that i already matched with first 'RET' record in File-1. So i planned like this.

Step1: Before starting matching porgram, i will load File-2 like this. Actualy data that it contains and i add new field called FLAG and move 'N' by default. So if the length if File-2 is 500 bytes then i add 501 and move value as 'N' for all the records.

Step2: As soon as match(Same Acct-Num,Trans and Chk-amt is matched) is found in File-2 for record present in File-1 then i check this flag in File-2, if it is 'N' then i make it as 'Y'. So when i come for new record in File-1 that contains same values of its prvious record, as per the logic everything matches for the second record in File-1 with File-2 but when i check Flag, it is 'Y' so its clear that its already matched for some other record in file-1. So i skip that record and keep searching to find same 'Acct-num'.

Here i have to read both the files sequentially. Then
only i can make it. Else program goes in loop. Please send me some sample programs two read two files sequentially and process. i want complete this asap. pls help me dears...
Back to top
View user's profile Send private message
sasikumar1984

Active User


Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India

PostPosted: Mon Jul 09, 2007 3:09 pm    Post subject:
Reply with quote

Hi,

My File-1 is a length of 2984, but i use above mentioned values(variable only). File-2 lenght is 500 where the above mentioned fileds only matched with that of File-1. thats what i gave examples like that. 'Trans' means Transactions. SO Same account holder do different kinds of transactions.
So i get same 'Acct-Num' with different 'Trans' and 'Chk-Amt'. SO for every read in File-1, i have to search the 'Acct-Num' once i get i have to validate other two values. If other two values also matched then i want this record moved to 'Matched' File. If i dont find 'Trans' and 'Chk-Amt' for all the occurrence of the same 'Acct-Num' in File-2, then i wanted this record moved to 'Unmatched' File. Here one more point, Same 'Acct-Num' can perform more than one 'RET' transactions with same amount. In that case i get two records in File-1. File-2 how i get is once it is processed only. So if any one of 'RET' is not processed then i dont get it File-2. But
File-1 contains two of 'RET's so when i get second RET from File-1 how i come to know that i already matched with first 'RET' record in File-1. So i planned like this.

Step1: Before starting matching porgram, i will load File-2 like this. Actualy data that it contains and i add new field called FLAG and move 'N' by default. So if the length if File-2 is 500 bytes then i add 501 and move value as 'N' for all the records.

Step2: As soon as match(Same Acct-Num,Trans and Chk-amt is matched) is found in File-2 for record present in File-1 then i check this flag in File-2, if it is 'N' then i make it as 'Y'. So when i come for new record in File-1 that contains same values of its prvious record, as per the logic everything matches for the second record in File-1 with File-2 but when i check Flag, it is 'Y' so its clear that its already matched for some other record in file-1. So i skip that record and keep searching to find same 'Acct-num'.

Here i have to read both the files sequentially. Then
only i can make it. Else program goes in loop. Please send me some sample programs two read two files sequentially and process. i want complete this asap. pls help me dears...
Back to top
View user's profile Send private message
sasikumar1984

Active User


Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India

PostPosted: Mon Jul 09, 2007 3:23 pm    Post subject:
Reply with quote

Hi,

My File-1 is a length of 2984, but i use above mentioned values(variable only). File-2 lenght is 500 where the above mentioned fileds only matched with that of File-1. thats what i gave examples like that. 'Trans' means Transactions. SO Same account holder do different kinds of transactions.
So i get same 'Acct-Num' with different 'Trans' and 'Chk-Amt'. SO for every read in File-1, i have to search the 'Acct-Num' once i get i have to validate other two values. If other two values also matched then i want this record moved to 'Matched' File. If i dont find 'Trans' and 'Chk-Amt' for all the occurrence of the same 'Acct-Num' in File-2, then i wanted this record moved to 'Unmatched' File. Here one more point, Same 'Acct-Num' can perform more than one 'RET' transactions with same amount. In that case i get two records in File-1. File-2 how i get is once it is processed only. So if any one of 'RET' is not processed then i dont get it File-2. But
File-1 contains two of 'RET's so when i get second RET from File-1 how i come to know that i already matched with first 'RET' record in File-1. So i planned like this.

Step1: Before starting matching porgram, i will load File-2 like this. Actualy data that it contains and i add new field called FLAG and move 'N' by default. So if the length if File-2 is 500 bytes then i add 501 and move value as 'N' for all the records.

Step2: As soon as match(Same Acct-Num,Trans and Chk-amt is matched) is found in File-2 for record present in File-1 then i check this flag in File-2, if it is 'N' then i make it as 'Y'. So when i come for new record in File-1 that contains same values of its prvious record, as per the logic everything matches for the second record in File-1 with File-2 but when i check Flag, it is 'Y' so its clear that its already matched for some other record in file-1. So i skip that record and keep searching to find same 'Acct-num'.

Here i have to read both the files sequentially. Then
only i can make it. Else program goes in loop. Please send me some sample programs two read two files sequentially and process. i want complete this asap. pls help me dears...
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Mon Jul 09, 2007 5:31 pm    Post subject:
Reply with quote

Hi,

First of all, you need to pre-sort all 3 fields. From what I see, the external sort sequences should be account number, amount, then trans. Your program probably goes into a loop because you are trying to read beyond the end-of-file. Once you reach end-of-file on either file, move high-values to the fields that you are matching on. Put a test in your program that says that if either matched key fields are high-values, do not read that file.

Acct-Num Trans Chk-Amt
1817415 WDL 1000 record 1 file 1
1817415 RET 2000 record 2 file 1
1817415 RET 2000 record 3 file 1
1817415 RET 2000 record 4 file 1
1817415 RET 2000 record 5 file 1

FILE-2 (This is how my File-2 which i get from 3rd party)

Acct-Num Trans Chk-Amt
1817415 WDL 1000 record 1 file 2
1817415 RET 2000 record 2 file 2
1817415 RET 2000 record 3 file 2
1817415 RET 2000 record 4 file 2
high values (pretending we have reached the end of this file)

Based on this scenario, what does your output look like?
Back to top
View user's profile Send private message
sasikumar1984

Active User


Joined: 02 Jul 2007
Posts: 109
Location: Chennai - India

PostPosted: Mon Jul 09, 2007 6:52 pm    Post subject: Re: Need to match the records from first file to second file
Reply with quote

No Sandy,

i just gave few records. i will be getting different 'Acct-Num'. Not a single Acct-Num' ya.

File-1

File - 1

Acct-Num Trans Chk-Amt
1817415 RET 2000
1817415 RET 2000
1817415 WDL 1000
1817415 RET 2000
1817415 RET 2000
1817419 TOV 3000
1817419 WDL 5000

See above eg: after 1817415 i get new account num.

Pls give me some example programs first.. so i start coding then i ask doubts..
Back to top
View user's profile Send private message
Sandy Zimmer

Active Member


Joined: 13 Jun 2007
Posts: 826
Location: Wilmington, DE

PostPosted: Mon Jul 09, 2007 7:07 pm    Post subject:
Reply with quote

I do not have a "sample" program. Each time you read either file, you need to check if there is a break in any of the 3 fields. Set up a working storage area for each file for each key. If you can get the break logic correct on just one customer, the rest will be fairly easy.
Back to top
View user's profile Send private message
gm_laks

New User


Joined: 10 Jul 2007
Posts: 7
Location: banagalore

PostPosted: Tue Jul 10, 2007 11:14 pm    Post subject:
Reply with quote

First sort file-1 and file-2 by acct num, trans and then by the amount.

in the program
Code:
perform Read file 1 para
perfom Read file 2 para
then compare the file using acct num,trans and the amt as the key
Code:
perform compare para till eof file-1


Code:
compare-para.
If key-of-file-1 > key-of-file-2
    perform read file-2 para
else
   if key-of-file-1 < key-of-file-2
      write rec-of-file-1 to unmatch-file
      perform read file-1 para
   else
       write rec-of-file-1 to match-file
       perform read file-1 para
       perform read file-2 para
   end-if
end-if

readfile-1 para.
read file
  at end
      move 'y' to eof file1

read-file-2 para.
if eof file 2
   next sentence
else
   read file2
    at end
        move 'y' to eof file 2
        move all '9'  to key-of-file-2
end-if.


Here i am assuming that when there are two occurences of the same tran with the same amount for an account in file-1 and only occurence in file-2 you will write one record to the match file and the other one to the unmatched file.

Hope this helps you.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jul 10, 2007 11:24 pm    Post subject:
Reply with quote

Hello,

If you want a working sample, please look at the "sticky" in this COBOL forum.

There is a complete downloadable sample program.

Something i will add to the sticky is that to keep things simple you do not want to code for 3 keys. As you read records (before any comparing) combine the 3 fields into a ws field so the compare does not become other than simple.
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us