DSXchange

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 ?

Use the UNIX command cat file1 file2 > file3

You don't need DataStage for this one.

ray.wurlod wrote:Use the UNIX command cat file1 file2 > file3

You don't need DataStage for this one.

It cannot be done through DataStage ?

ray.wurlod wrote:Use the UNIX command cat file1 file2 > file3

You don't need DataStage for this one.

Basically the requirement is cartesian join between two files.

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.

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.

But a merge stage requires a key to defined for the two files on the basis of which the merging will be done.

ANY join, Cartesian or otherwise, requires a key upon which to base the join.

Generate a key (line number) if you must.

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.

There isn't any matching column between the two tables to join on that basis. "Generate a key (line number) if you must" please explain these one.

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.

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.

pravin1581 wrote:

ray.wurlod wrote:Use the UNIX command cat file1 file2 > file3

You don't need DataStage for this one.

Basically the requirement is cartesian join between two files.

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".

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.

pravin1581 , for more clarification, give us a couple of records from both the files and how it will look after transformation.

chulett wrote:

pravin1581 wrote:

ray.wurlod wrote:Use the UNIX command cat file1 file2 > file3

You don't need DataStage for this one.

Basically the requirement is cartesian join between two files.

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".

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.

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.

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.

This doesn't help at all to explain your requirements.

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.

....and for this very reason, I requested the OP to provide sample data for clarity.

DSguru2B wrote:....and for this very reason, I requested the OP to provide sample data for clarity.

The scenario is something like this :

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.