topic Re: Joining same table with two different key sets in Talend Studio

Joining same table with two different key sets

_AnonymousUser — Fri, 18 Jan 2013 09:29:42 GMT

Hi,
I have some scenario, which I am trying to find out solution in Talend.
two tables
Table A COL1 -- Main table
Table B COL1,COL2,COL3 -- Lookup table
The condition is,
JOIN A.COL1 = B.COL1
IF COL3 IS NULL
THEN JOIN A.COL1 = B.COL2 POPULATE COL3 IF NOT NULL
I tried this scenario using one input , tmap and two lookups. the two lookups are from same table with different set of keys (B COL1, B COL3 & B COL2, B COL3) then joining with the source and written the same join condition in the output in the tmap.
But I am trying to aviod using two lookups from the same table. Trying to use only one lookup and to split the two set of outputs and to connect to same tmap.
Can anybody help me?
Thanks in Advance.

Re: Joining same table with two different key sets

_AnonymousUser — Fri, 18 Jan 2013 12:32:55 GMT

Is there anybody ? please help me.

Re: Joining same table with two different key sets

_AnonymousUser — Mon, 21 Jan 2013 07:03:00 GMT

Can anybody help me to resolve the issue?

Re: Joining same table with two different key sets

Anonymous — Mon, 21 Jan 2013 07:50:31 GMT

Could you please add a screen shot of what you have developed for better clarity?
Warm regards,
Madhao

Re: Joining same table with two different key sets

_AnonymousUser — Mon, 21 Jan 2013 08:01:15 GMT

Hi,
Table A Main table
C1
1
2
3
4
5
Table B Lookup table
C1,C2,C3
1,2,null
2,1,WORLD2
3,6,WORLD3
4,3,WORLD4
5,8,WORLD5

if A.C1 = B.C1 in this condition if C3 has value then populate else A.C1=B.C2 then populate C3.
for example, A.C1 =1 --> B.C1= 1 then look for value C3, its null.. so change the condition as A.C1 =1 --> B.C2=1, then look for C3 value now, it has WORLD2 as value, so populate the same. so it goes on..
Thanks,
Karthikeyan J.

Re: Joining same table with two different key sets

Anonymous — Mon, 21 Jan 2013 09:01:48 GMT

Hi kartik,
You got me wrong.I got that,i was asking if you have developed someting in tmap already.
Anyways,if i were you i would perfer to do this in the DB using sql insted of tMap it would be easier and faster.
Warm regards,
Madhao

Re: Joining same table with two different key sets

Anonymous — Mon, 21 Jan 2013 10:39:48 GMT

karthikeyan you can do it with help of splitting file into two output. do like below.

your input ----------tMap------tjava------tmap-withlookup
|
|
tJava
|
tMap-with Lookup

check in tMap if C3 has value if yes then redirect that rows to true part
A.C1 = B.C1
else pass to second tmap for join A.C1=B.C2 to populate C3.
I this will help you....

Re: Joining same table with two different key sets

_AnonymousUser — Mon, 21 Jan 2013 12:11:03 GMT

Hi Umesh,
Thanks for your input.
As per your reply, I need to use two inputs as lookup which I have tried. But my aim is to use only one input as lookup and to create two key output key sets, which would reduce the loading the same data twice in the looktup.
Thanks.
Karthikeyan J.

Re: Joining same table with two different key sets

Anonymous — Mon, 21 Jan 2013 12:20:26 GMT

C3 column has NULL then is it populating with some default value?

Re: Joining same table with two different key sets

Anonymous — Tue, 22 Jan 2013 07:02:12 GMT

Hi Kartik,
You can use the following
your input B-----tjavarow----- tmap and other flow
|
input A
In tjavarow you can create an extra column C4 where you will populate values as per your requirement(i.e.
if C3 has value then B.c1 else B.c2)
now use this column C4 as the joining key to join with A.c1

Hope it helps
Warm regards,
Madhao