View previous topic :: View next topic
|
Author |
Message |
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
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 |
|
|
Sandy Zimmer
Active Member
Joined: 13 Jun 2007 Posts: 826 Location: Wilmington, DE
|
|
|
|
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 |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
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 |
|
|
Sandy Zimmer
Active Member
Joined: 13 Jun 2007 Posts: 826 Location: Wilmington, DE
|
|
|
|
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. |
|
Back to top |
|
|
Sandy Zimmer
Active Member
Joined: 13 Jun 2007 Posts: 826 Location: Wilmington, DE
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
|
|
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 |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
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 |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
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 |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
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 |
|
|
Sandy Zimmer
Active Member
Joined: 13 Jun 2007 Posts: 826 Location: Wilmington, DE
|
|
|
|
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 |
|
|
sasikumar1984
Active User
Joined: 02 Jul 2007 Posts: 109 Location: Chennai - India
|
|
|
|
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 |
|
|
Sandy Zimmer
Active Member
Joined: 13 Jun 2007 Posts: 826 Location: Wilmington, DE
|
|
|
|
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 |
|
|
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
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
|