DSXchange

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.

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?

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?

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.

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

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

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

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.

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.

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