View previous topic :: View next topic
|
Author |
Message |
faizm
New User
Joined: 13 Apr 2012 Posts: 59 Location: India
|
|
|
|
Hi
I have two files of same fields and LRECL (100), F1(previous day file) and F2(current day file). I have a account number field (first 3 char, ex AAA in F1 file) and beneficiaries field along with other data fields in both files. There are multiple records for a particular account upto number of beneficiaries present on this policy.
Now I want to compare these two files on full length and write record from current day file i.e from F2 if there is a change from F1 file. criteria is , if any of the record on a particular account is changed in F2 than from F1,then write all records available on that account from F2 file in output file.
I have used JOIN UNPAIRED,F2,ONLY, which is only sending the unmatched record on any account.
How we can send all the records of a particular account from F2 if there is a change on any record on this account.
EX:
Code: |
F1 File:
AAA Mr Mark 01012014 25
AAA Mr Rose 02012014 75
BBB Mr Mike 01012014 50
BBB Mr Luke 01012014 50
F2 File
AAA Mr Mark 01012014 25
AAA Mr Rose 02012014 75
BBB Mr Mike 01012014 25
BBB Mr Luke 01012014 50
BBB Mr Hawk 04012014 25
So output should be (AAA records are exactly matched so we will not write records of AAA)
BBB Mr Mike 01012014 25
BBB Mr Luke 01012014 50
BBB Mr Hawk 04012014 25
|
Please help. Thank you. |
|
Back to top |
|
|
Dsingh29
Active User
Joined: 16 Dec 2008 Posts: 132 Location: IBM
|
|
|
|
UNPAIRED must give you record
BBB Mr Hawk 04012014 25
now in the second pass, take its account no. BBB and write all duplicates/matches from first file.
Your output then will have all 3 records of BBB. |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
And what if two BBBs show differences? |
|
Back to top |
|
|
faizm
New User
Joined: 13 Apr 2012 Posts: 59 Location: India
|
|
|
|
Bill,
yes, this is my requirement. F2 file will always have the changed record from the previous day F1 file if there are any changes in any record. So, we need to pick up these changed record and also other un changed available records of BBBs and write these. If all the records of any account is exactly matched then we should totally skip all records of this account.
Thanks |
|
Back to top |
|
|
faizm
New User
Joined: 13 Apr 2012 Posts: 59 Location: India
|
|
|
|
Hey Dave
Can you elaborate on second part that you suggested. I tried but not getting the desired result.
Thanks |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
He means it is a two-stage process. First, find the differences. Second, de-duplicate the differences (possibly, up to you, we don't know...) and extract those groups of records which contain a difference.
First stage you SORT and match on the whole record. Second stage de-dupe and match one the key, SORTing if you don't have your key starting in position one on the record and being contiguous. |
|
Back to top |
|
|
faizm
New User
Joined: 13 Apr 2012 Posts: 59 Location: India
|
|
|
|
Bill,
I did not get that. I have the changed records that are in file F2. Now I also want other records of this account which are not changed.
I used JOIN UNPAIRED,F2,ONLY which results in only difference/changed record.
I have written another SORT step which takes the F1 file and new file created in prev step (file with changed record).
Can you pls let me know how I should move ahead from here. Thanks |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
You match on entire records to find the differences. The differences would show in two ways: unmatched records from F1; unmatched records from F2.
If you want all the records from F2 which have any unmatched records from that group, then you need UNMATCHED,F2 to an output dataset.
Then another step, or separate process, which de-duplicates that file, and uses the de-duplicated file to match, by key, on F2.
If that is what you want. Unclear to me.
If still stuck, put representative sample data for F1 and F2 and expected output from the process. |
|
Back to top |
|
|
faizm
New User
Joined: 13 Apr 2012 Posts: 59 Location: India
|
|
|
|
Bill
I given the sample data and expected output in my first post. Thanks |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
So that is representative? Nothing other than those two situations to cater for?
Inserts only, no amendments, no deletes?
One JOINKEYS to get the inserts. Second JOINKEYS to match the inserts to the F2 to get all the records of the same key. Simple. Really simple. Just as Dave wrote earlier. What was that, hours ago? |
|
Back to top |
|
|
faizm
New User
Joined: 13 Apr 2012 Posts: 59 Location: India
|
|
|
|
'Second JOINKEYS to match the inserts to the F2 to get all the records of the same key' - this is where I am stuck. Since I am new to joinkeys, i dont have much idea of inserting from one file to other. I tried reading some material but didnt get example. Thanks |
|
Back to top |
|
|
faizm
New User
Joined: 13 Apr 2012 Posts: 59 Location: India
|
|
|
|
Thank you. It is working now. |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Good for you. Half an hour well-spent. If you'd like to post your solution, it may help others. |
|
Back to top |
|
|
|