Talend community forum / lookup vs join in tmap

animesh · 2012-02-21 06:58:44

Hi,
I need to do a lookup for certain values from a table.I have attached a sample problem for easy understanding.
i need to lookup row1.number1 & row1.number2 against row2.number.The condition is,
row1.number1 and row1.number2 should not be present in row2.number.
If they are not present then the value should be populated in out1, otherwise out1.number1 and out1.number2 should be populated with NULL.

Any help would be highly appreciated.

Thanks,
animesh

Uploaded Images

pedro · 2012-02-21 07:08:25

Hi Animesh

You can set the expression of OUT1 as follow.

Code:

(row1.number1 != row2.number)&&( row1.number2 != row2.number)

Regards,
Pedro

animesh · 2012-02-21 07:23:41

Thanks Pedro!

But I am not sure if this will work according to my requirement because i dont want to loose and data rows.
There can be a case that (row1.number1 != row2.number) and (row1.number2 = row2.number) or vice versa. In this case i want
OUT1.number1 = row1.number1
OUT1.number2 = NULL

pedro · 2012-02-21 07:29:02

Hi

Got it.
Set expression of number1 in OUT1.

Code:

row1.number1==row2.number?null:row1.number1

Set expression of number2 in OUT1.

Code:

row1.number2==row2.number?null:row1.number2

Regards,
Pedro

animesh · 2012-02-21 08:07:51

Hi,
The result was not expected...it ended up with a cross join b/w row1 and row2.
Do we need to always specify the join type when using a lookup in tmap?

Regards,
animesh

pedro · 2012-02-21 08:14:04

Hi Animesh

Don't use any join here.

Regards,
Pedro

Uploaded Images

animesh · 2012-02-21 10:04:09

Hi,
I just figured it out that when we are using a lookup table in tmap it will perform a left outer join between the two tables (which is the default join type) wether we specify any keys or not.
hence in this case what is happening is it is making a left outer join between row1 and row2 which is not desirable. I just need all the rows of row1 by looking up the data from row2.

animesh · 2012-02-21 10:35:00

my point is can we perform a simple lookup on data without doing a join in tmap?

pedro · 2012-02-21 11:04:00

Hi

No. Because you have to set expressions.
Here is a workaround.
Input Data

Code:

1;2
3;4
5;6

Lookup

Code:

3
4

Regards,
Pedro

Uploaded Images

Announcement

Unanswered posts

#1 2012-02-21 06:58:44

lookup vs join in tmap

#2 2012-02-21 07:08:25

Re: lookup vs join in tmap

Code:

#3 2012-02-21 07:23:41

Re: lookup vs join in tmap

#4 2012-02-21 07:29:02

Re: lookup vs join in tmap

Code:

Code:

#5 2012-02-21 08:07:51

Re: lookup vs join in tmap

#6 2012-02-21 08:14:04

Re: lookup vs join in tmap

#7 2012-02-21 10:04:09

Re: lookup vs join in tmap

#8 2012-02-21 10:35:00

Re: lookup vs join in tmap

#9 2012-02-21 11:04:00

Re: lookup vs join in tmap

Code:

Code:

Board footer