Need to match the records from first file to second file

sasikumar1984 · Posted: Sun Jul 08, 2007 11:04 am

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???

Sandy Zimmer · Posted: Sun Jul 08, 2007 6:58 pm

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?

sasikumar1984 · Posted: Sun Jul 08, 2007 10:50 pm

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...

Sandy Zimmer · Posted: Sun Jul 08, 2007 11:46 pm

How large would you make that array?

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.

Sandy Zimmer · Posted: Sun Jul 08, 2007 11:48 pm

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.

dappyin · New User Joined: 23 Dec 2006 Posts: 2 Location: Gurgaon

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.

dick scherrer · Posted: Mon Jul 09, 2007 3:17 am

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

stodolas · Posted: Mon Jul 09, 2007 7:25 am

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>

sasikumar1984 · Posted: Mon Jul 09, 2007 7:55 am

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....

dick scherrer · Posted: Mon Jul 09, 2007 8:44 am

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.

sasikumar1984 · Posted: Mon Jul 09, 2007 2:52 pm

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...

sasikumar1984 · Posted: Mon Jul 09, 2007 3:09 pm

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...

sasikumar1984 · Posted: Mon Jul 09, 2007 3:23 pm

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...

Sandy Zimmer · Posted: Mon Jul 09, 2007 5:31 pm

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?

sasikumar1984 · Posted: Mon Jul 09, 2007 6:52 pm

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..

Sandy Zimmer · Posted: Mon Jul 09, 2007 7:07 pm

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.

gm_laks · New User Joined: 10 Jul 2007 Posts: 7 Location: banagalore

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

in the program

dick scherrer · Posted: Tue Jul 10, 2007 11:24 pm

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.