Merging Records
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
Merging Records
Hi All,
How can we merge records from two files and load in a single file . Basically the requirement is to append the two files in a single file ?
How can we merge records from two files and load in a single file . Basically the requirement is to append the two files in a single file ?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Cartesian join (your second post) is a different requirement from union all (your first post - "append the two files in a single file").
If file1 has N lines and file2 has M lines, the approach I suggested will yield (N + M) lines. A Cartesian join will yield (N * M) lines. Which do you want?
You can use a Merge stage in server jobs to perform joins between two text files.
If file1 has N lines and file2 has M lines, the approach I suggested will yield (N + M) lines. A Cartesian join will yield (N * M) lines. Which do you want?
You can use a Merge stage in server jobs to perform joins between two text files.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
But a merge stage requires a key to defined for the two files on the basis of which the merging will be done.ray.wurlod wrote:Cartesian join (your second post) is a different requirement from union all (your first post - "append the two files in a single file").
If file1 has N lines and file2 has M lines, the approach I suggested will yield (N + M) lines. A Cartesian join will yield (N * M) lines. Which do you want?
You can use a Merge stage in server jobs to perform joins between two text files.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
I am giving you the full requirement , suppose there are three columns in a file and another file has 3 columns . I want 1 columns from the first file and 1 column from another file. Select A from B and C from D. I hope the requirement is pretty clear now. The output file should contain the reqd. columns from both the files.ray.wurlod wrote:ANY join, Cartesian or otherwise, requires a key upon which to base the join.
Generate a key (line number) if you must.
Make sure this is really what you want before you go further down the path - a cartesion join. Reread Ray's explaination of the difference as it is totally different from your original request to "merge records from two files and load in a single file".pravin1581 wrote:Basically the requirement is cartesian join between two files.ray.wurlod wrote:Use the UNIX command cat file1 file2 > file3
You don't need DataStage for this one.
If you just want to 'merge' the two files, choose Complete Set as the Join Type. Or just cat the dang things together before job and then process the cat'd file - that's a very common solution and preferrable to use of the Merge stage. IMHO.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
I am unable to use the merge stage as it requires a key to be defined for joining , which is not present in my case . I require the SQL select A from B and C from D to generate the output file.chulett wrote:Make sure this is really what you want before you go further down the path - a cartesion join. Reread Ray's explaination of the difference as it is totally different from your original request to "merge records from two files and load in a single file".pravin1581 wrote:Basically the requirement is cartesian join between two files.ray.wurlod wrote:Use the UNIX command cat file1 file2 > file3
You don't need DataStage for this one.
If you just want to 'merge' the two files, choose Complete Set as the Join Type. Or just cat the dang things together before job and then process the cat'd file - that's a very common solution and preferrable to use of the Merge stage. IMHO.
This doesn't help at all to explain your requirements.pravin1581 wrote:I am unable to use the merge stage as it requires a key to be defined for joining , which is not present in my case . I require the SQL select A from B and C from D to generate the output file.
At a high level, let's say your two files have record counts like this:
FileA: 2 records
FileB: 10 records
How many records are you expecting in your output? 2? 10? 12? 200? All are valid answers and require different solutions to implement.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
The scenario is something like this :DSguru2B wrote:....and for this very reason, I requested the OP to provide sample data for clarity.
File A File B
--------- --------
UPC ORG StORE DATE
01 ALB 001 01/24/2007
02 ALB 002 01/25/2007
03 ALB 003 01/26/2007
04 ALB 004 01/27/2007
05 ALB 005 01/28/2007
The output file contains the following fields and the corresponding values are :
File C
---------
UPC ORG StORE DATE
01 ALB 001 01/24/2007
02 ALB 002 01/25/2007
03 ALB 003 01/26/2007
04 ALB 004 01/27/2007
05 ALB 005 01/28/2007
01/29/2007
01/30/2007
01/31/2007
I hope it is clear now .Thanx in advance.