View previous topic :: View next topic
|
Author |
Message |
jegan_selvan
New User
Joined: 23 Oct 2006 Posts: 24 Location: India
|
|
|
|
Hello All,
Could you please kindly help me on the below requirement.
I have two input Files with same record length and record format.
And also one input file with the key in File 1 and key in File 2.
File 1 as well as File 2 - FB - 80.
1. Name ( x(15) )
2. Personal Number + Suffix ( x(13) = 10 + 3 )
3. Amount1 ( 9(5).9(2) )
4. Amount2 ( 9(5).9(2) )
5. Remaining other fields
Compare File :
1. Personal Number + Suffix ( x(13) = 10 + 3 ) from File 1
2. Personal Number + Suffix ( x(13) = 10 + 3 ) from File 2
Output File :
1. Should take the Personal Number with Suffix value from Compare file and retrieve the Personal Number + Suffix,Amount1,Amount2 from File 1.
2. Should take the Personal Number with Suffix value from Compare file and retrieve the Personal Number + Suffix,Amount1,Amount2 from File 2.
3. Write output file with format mentioned below.
Output File : FB - 80
1. Personal Number + Suffix - File 1
2. Personal Number + Suffix - File 2
3. Amount 1 from File 1
4. Amount 1 from File 2
5. Amount 2 from File 1
6. Amount 2 from File 2
Both the Files will have multiple entries of Personal Number + Suffix with different Amount values.
Example File 1:
Code: |
File 1 :
Personal Num + Suffix Amount1 Amount 2
0000000001XAB 32366.20 32366.20
0000000001XAB 38206.84 38206.84
0000000001XBB 43507.32 43507.32
0000000001XCB 46726.52 46726.52
0000000001XCB 55180.92 55180.92
Fiel 2 :
Personal Num + Suffix Amount1 Amount 2
0000000005YAB 77366.20 77366.20
0000000005YAB 35606.84 35606.84
0000000005YBB 46707.32 46707.32
0000000005YCB 49726.52 49726.52
Compare File :
0000000001XAB 0000000005YAB
Output File :
0000000001XAB 0000000005YAB 32366.20 77366.20 32366.20 77366.20
0000000001XAB 0000000005YAB 38206.84 35606.84 38206.84 35606.84
0000000001XBB 0000000005YBB 43507.32 46707.32 43507.32 46707.32
0000000001XCB 0000000005YCB 46726.52 49726.52 46726.52 49726.52
0000000001XCB 0000000005YCB 55180.92 55180.92
|
|
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
You'll need to be clearer about how you get that output. There's only one suffix for each file on your third input, but you show multiple suffixes for your output. |
|
Back to top |
|
|
jegan_selvan
New User
Joined: 23 Oct 2006 Posts: 24 Location: India
|
|
|
|
Hello,
Many thanks for your reply. Sorry for the post I have shown only one compare record.
Actual Compare Record will be :
Code: |
Compare File :
0000000001XAB 0000000005YAB
0000000001XBB 0000000005YBB
0000000001XCB 0000000005YCB |
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3051 Location: NYC,USA
|
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
I think you'll need two JOINKEYS steps, due to the duplicate keys on your input files.
First JOINKEYS to match the key file to File 1, extracting only the information needed (amounts, key, and other-key) then second step to match on other-key. Even then you'll have some excitement with the multiple keys, but make a start with that and see how it goes. |
|
Back to top |
|
|
|