Unique requirement to combine two files

reddy · Post by **reddy** » Thu Sep 03, 2009 11:33 am

Hi,
I have a requirement to combine records from two input files, none of which is a Master. I want records from both merged, but on the same line.

Input file 1
53,red
53,blue
55,black
57,orange
57,violet

Input file 2
53,boy
53,cat
53,dog
54,pig
55,rat
56,girl

Expected Output is
53, red, boy
53, blue, cat
53, ,dog
54, , pig
55,black,rat
56, , girl
57, orange,
57, violet,

Any help is appreciated. Thanks.

ArndW · Post by **ArndW** » Thu Sep 03, 2009 11:47 am

Why not use an inner join in a join stage or a lookup stage (choose either link as a master) and have both Col2's go to the output?

hiral.chauhan · Post by **hiral.chauhan** » Thu Sep 03, 2009 12:18 pm

You might want to use a Look up stage like ArndW said, choose either link as a Master, Store the output of both the links, and concatenate them on their way to the target table/file. Does that make sense?

ray.wurlod · Post by **ray.wurlod** » Thu Sep 03, 2009 3:18 pm

ArndW wrote:Why not use an inner join in a join stage or a lookup stage (choose either link as a master) and have both Col2's go to the output?

A full outer join would be needed to achieve all of the output specified. Other than that quibble I, too, would recommend using a Join stage.

RAJARP · Post by **RAJARP** » Thu Sep 03, 2009 5:43 pm

Hi Reddy,

I/P file 2------- >Join Stage--------->Target

I/P file1-------->(connect this link to Join Stage)

Set the properties of Join stage as follows:

Key:column containing value 53,54 etc
Join Type:left outer join

Link ordering:
---------------
Left:Link from I/p file 2
Right:Link from I/p file1

Try this.

Regards,
Raja R P

dxk9 · Post by **dxk9** » Thu Sep 03, 2009 10:57 pm

Just join the Key column with full outer join.This will give the desired output.

a. Input1 (fa,fb)
b. Input2 (fc,fd)
c. Join fa,fc (full outer join)
d. Pull fk(fa,fb),fc,fd to target.

Regards,
Divya

laknar · Post by **laknar** » Fri Sep 04, 2009 2:26 am

why don't use the funnel stage
give union option there

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Sep 04, 2009 3:29 am

You cannot directly use a join stage as you have duplicate values which will result in cartesian join.

You need to add another a running sequence number and include it in your join.

RohitNara · Post by **RohitNara** » Thu Sep 17, 2009 6:24 am

reddy wrote:Hi,
I have a requirement to combine records from two input files, none of which is a Master. I want records from both merged, but on the same line.

Input file 1
53,red
53,blue
55,black
57,orange
57,violet

Input file 2
53,boy
53,cat
53,dog
54,pig
55,rat
56,girl

Expected Output is
53, red, boy
53, blue, cat
53, ,dog
54, , pig
55,black,rat
56, , girl
57, orange,
57, violet,

Any help is appreciated. Thanks.

You can add the column say "Srno" to both the table in the transformer prior to the Join Stage. Data from both the table will look like
srno Column1 colum2
1,53,red
2,53,blue
1,55,black
1,57,orange
2,57,violet

and for second table
srno Column1 Column2
1,53,boy
2,53,cat
3,53,dog
1,54,pig
1,155,rat
1,56,girl

Logic to assign the values to "Srno" column as shown above is already posted on the forum.

You can use "FULL OUTER JOIN" in the join stage with key fields as "Srno" and column contains 53,54 ...

I hope this will give you the desired output.

keshav0307 · Post by **keshav0307** » Fri Sep 18, 2009 1:45 am

i implememnted the same requirement, using oracle rank function and full outer join

DSXchange

Unique requirement to combine two files

Unique requirement to combine two files

Re: Unique requirement to combine two files