left outer join in tMap

[Anonymous]

Hi !
I searched the forum first, but did not find an answer. It seems that in tMap, when performing a left outer join, the filter of the lookup table is not considered. Which means that I end up with way too many records in the resulting output. More precisely, I have the following table in the main flow :

id, space, position
1, X, 910845
2, X, 467963
3, X, 1919947
4, Y, 467963

and the following table for the lookup flow :

id, space, coord1, coord2
1, X, 867096, 894061
2, X, 927586, 973659
3, X, 469127, 517230

and I want to match all entry in the main flow for which the position falls between lookup flow coord1 - 20000 and lookup flow coord2 + 20000 and which belong to the same space. In SQL, my query is :

select * from main left join lookup on main.space=lookup.space and (lookup.coord1 - main.position) < 20000 and (main.position - lookup.coord2) < 20000;

in tMap, I generated a key between fields main.space and lookup.space, but I used a filter for the rest, since it is not one-to-one field correspondance.
Executing the above SQL yields :

1, X, 910845, 1, X, 867096, 894061
1, X, 910845, 2, X, 927586, 973659
2, X, 467963, 3, X, 469127, 517230
3, X, 1919947,,,,
4, Y, 467963,,,,

and I should get the same out of tMap, but instead I get :

1, X, 910845, 1, X, 867096, 894061
1, X, 910845, 2, X, 927586, 973659
1, X, 910845,,,,
2, X, 467963,,,,
2, X, 467963,,,,
2, X, 467963, 3, X, 469127, 517230
3, X, 1919947,,,,
3, X, 1919947,,,,
3, X, 1919947,,,,
4, Y, 467963,,,,

Which is incorrect. Ok, I do get populated fields corresponding to lookup only for the "right" corresponding main records (and empty fields otherwise), but I get too many total records. In fact I get the same records as when I execute my previous SQL statement with a join condition on space only :

select * from main left join lookup on main.space=lookup.space ;

So, finally, the question is : what am I missing ? How can I get tMap to give me only the same 5 records I get with SQL ?
Thanks !

[Anonymous]

You should left join ( all matches ) on "space" on tmap and then do the filtering in the output table ( interval ).
Post your tmap screenshot in case.

[Anonymous]

Hi !
If I am not mistaken, I do use a left join with all matches on space and then filters for the interval. I attached the screen shot.

Thanks.

[Anonymous]

You should place your filter conditions in the output table ( right half screen ), not on the input.

[Anonymous]

Hi !
I moved the filter to the output table. Now, I only get records which have an entry in both the main and lookup flows. Records which have an entry in the main flow but not in the lookup flow are dropped. In other words, the equivalent of a SQL join is performed, not that of a left join. Here is what I get :

1,X,910845,1,X,867096,894061
1,X,910845,2,X,927586,973659
2,X,467963,3,X,469127,517230

And here is what I am looking for :

1, X, 910845, 1, X, 867096, 894061
1, X, 910845, 2, X, 927586, 973659
2, X, 467963, 3, X, 469127, 517230
3, X, 1919947,,,,
4, Y, 467963,,,,

Thanks.

[Anonymous]

Maybe the problem is that you are filtering such rows in the output filter ( because coord1 and coord2 default to zero thus are rejected by condition) ... you can solve checking if they are zero... ie.
( coord1 + coord2 == 0 ) || ( MY INTERVAL CONDITION )
anyway I think that
3, X, 1919947
is an outlier ... but you know your data...

[_AnonymousUser]

Hello everybody,
I am replying to this post because my problem is quite the same as statgen's one:
Here are my inputs:
- Main:
Key;Value
A;6
A;2
A;0
B;2
A;9
-Lookup:
Key;Value_min;Value_max
A;1;4
A;5;10
A;8;10
A;-10;-5
Two output flows: "Valid" and "Errors":
-Valid:
Key;Value;Value_min;Value_max
A;6;5;10
A;2;1;4
A;9;5;10
-Errors:
Key;Value;
A;0
B;2
The goal of the job is to find one and only one match to each input row (no matter if it is the first match or another one). The input rows for which I can find no match are considered as errors. By "match" I understand matching the key AND having the value between value_min and value_max.
Here is my job:


I understand why it does not work as I want it to: The "first match" option in the tMap component does not return the first match of both the join AND the filter, but returns the first match of the join, and THEN filter it, which cannot work properly.
However, I don't want to use the "all match" option because it will over-populate both my "valid" flow and my "error" flow.
Moreover, using a tUniqRow to filter out my duplicates is not an option because I can have duplicates in my input flow and I want to keep them as they are. (I still can create a "homemade" incremental key before the tmap but for performance reasons I wish I can avoid using a tUniqRow nor another tmap).
Does somebody have had the same problem ? Do have any workaround?
Thank you very much,
Simon B