Joined: 29 Dec 2009 Posts: 3 Location: Arkansas, USA
Hello.
I have a requirement to get the delta file on daily basis as well as maintain a master file. let me explain the requirement with example.
File 1: say this is master
111111 arizona 06440
222222 New York 11001
333333 Arkansas 72712
File 2 : say this is todays input file.
111111 arizona 74400
222222 New York 11001
444444 Arkansas 72756
In both the files first 6 bytes are the key and total record length is 25
What i need is,
Out1 : this should contain the records which are unique to File 1 and FIle2. Also it should have the updated record from file2, if the same key record is available on File1.
For example
Out 1 : i.e updated master file
111111 arizona 74400
222222 New York 11001
333333 Arkansas 72712
444444 Arkansas 72756
Out 2 : todays delta file
should contain only the records that are unique to File2.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Deepak,
Your example and description do not make sense.
You say that the key is positions 1-6, so that means 111111 is in both files, 222222 is in both files, 333333 is only in file1 and 444444 is only in file2. But then you talk about records that are unique to File1 (which would only be 333333) and unique to File2 (which would only be 444444), but the records you show for output don't match those cases.
You need to do a better job of explaining what you want to do and showing an example of input records and expected output records that correspond to your description of what you want to do.
Joined: 29 Dec 2009 Posts: 3 Location: Arkansas, USA
Frank,
Sorry for the confusion. let me try to explain it better. My example holds good with below descripton.
The files we are getting is the club membership information file, where first 6 bytes are the membership number, the next field is state and last field is zip code.
What we want to do it, maintain a master file (File1). Then we get a daily feed of changes to membership(File2), which include updates to existing memberships and also new memberships.
When we get a new membership we need to pass that new membership to the next process (Out2). At the same time write this record to the master file(Out1), so that master is up to date.
When we get an update to an existing membership, we want to pass this information to the next process(Out2) and at the same time want to update the master file(Out1).
The reason we want to maintain a master is avoid sending duplicate records to Out2. So if i get today a record and then get the same record on day 5, when I compare it with master file on day5, i will find it and will not write it to Out2.
Hope this clarifies. Please let me know if you need any more information
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
The confusion here is your use of the terms "key", "new", "update", "same".
What makes a record a "new" one? Which of the three fields in that record must be different from the corresponding fields in an existing record so that it will be considered "new". Must all three fields be different, or must two fields be different (which two) or must one field be different (which one).
Same question for what makes a record an "updated" one, and what makes a record the "same" one.
You need to explain clearly what you mean by a "new" record, an "updated" record and the "same" record in terms of the corresponding fields in file1 and file2, and relate that to your example of input and output records (a better example with all cases would help).
Joined: 29 Dec 2009 Posts: 3 Location: Arkansas, USA
Frank,
A record is considered as updated when either state or Zip code changes or both of them change in File2.
A record is new if the key field is new and available only in File2
A record is same when all 3 fields are same in File2 and File1
Below example of file2 shows what i mean by new, updated and same record.
File 1: say this is master
111111 arizona 06440
222222 New York 11001
333333 Arkansas 72712
File 2 : say this is todays input file.
111111 arizona 74400 --> Updated record, since this key is available in Master, but the zip code changed
222222 New York 11001 --> Same record (duplicate) - Since this Key is avaiable in master and there is no change to other 2 fields also .
444444 Arkansas 72756 --> New Record, Since this key is not available in Master