topic Re: [resolved] Error in tMap join expression using row variables from lookup input in Talend Studio

[resolved] Error in tMap join expression using row variables from lookup input

Anonymous — Fri, 15 Feb 2013 19:16:54 GMT

I'm trying to JOIN a main input (row1) and a lookup input (Bad_Ones) in a tMap component with three JOIN-ON expressions. Two of the expressions use only variables from the main input. They work fine.
But one of the JOIN-ON expressions needs to also use a variable from the lookup input. The Variable list in the Expression Builder offers every field from both the main and the lookup inputs as variables for use in the expressions (shown in image 3). But the code generated gets compilation errors on each use of the variables from the lookup row. It highlights the name of the lookup input (Bad_Ones) and reports that the lookup table name cannot be resolved.
There seems to be a problem in the way the code is generated for the JOIN.
Am I doing something wrong?
How can the fields from the lookup table be used in the Join-On expression?

Re: [resolved] Error in tMap join expression using row variables from lookup input

Anonymous — Sun, 17 Feb 2013 22:11:44 GMT

This might be a work around, but from what I can see in the screenshots, you might try to use a tMap early on to concatenate the firstname, lastname, and middlename first into a single field. You will need to do this for your lookup files as well. Then when you join, you will really only be joining on one field. Joining on one field, might get you past this issue.
also, make sure you trim and UCASE or whatever you want to to scrub the firstname, lastname, and middle name fields before you concatenate it.

Re: [resolved] Error in tMap join expression using row variables from lookup input

Anonymous — Tue, 19 Feb 2013 20:25:04 GMT

Thanks for your suggestion. I've already used the "earlier tMap processing" idea to improve the match (and every field has been normalized, including to UCASE, thanks). But it doesn't get me far enough.
I need to join on several fields, including middle name fields where values can be blank, an initial, or a complete middle name. I need to JOIN records if the complete middle names match, if the first letter of a complete middle name of *either* input row matches an initial in the other, or, if the field in *either* input row is blank. The best I've done so far is to (pre) trim both fields to initials, which achieves about 75% sensitivity (good matches detected) and 95% specificity (bad matches correctly not selected). The largest error (missed good matches) comes from the case where one of the input row middle name fields is blank and the other contains a value.
If I could use a field in the lookup row as a variable in the JOIN expression, like it seems should work, I'd have a really good JOIN.
Anybody have any suggestions?

Re: [resolved] Error in tMap join expression using row variables from lookup input

Anonymous — Tue, 19 Feb 2013 20:54:57 GMT

If I've seen it right you try to put a comment in tMap expression field and this is not supported.

Re: [resolved] Error in tMap join expression using row variables from lookup input

Anonymous — Tue, 19 Feb 2013 21:39:47 GMT

You may be right, but I've been putting comments in expressions regularly and they have not caused any problems. I've observed them in the generated code and they look just fine.
I don't think they are the cause of the "name cannot be resolved" errors.
Can someone from Talend comment on this?

Re: [resolved] Error in tMap join expression using row variables from lookup input

Anonymous — Thu, 21 Feb 2013 04:19:44 GMT

If I could use a field in the lookup row as a variable in the JOIN expression, like it seems should work, I'd have a really good JOIN.
Anybody have any suggestions?

You can't use the row name(Bad_Ones) as a variable in its expression in the look row.
Shong

Re: [resolved] Error in tMap join expression using row variables from lookup input

Anonymous — Tue, 26 Feb 2013 15:39:34 GMT

OK. Then maybe the Variable list in the Expression Builder should not offer the fields from the lookup row for use in lookup row expressions when they cannot be used (as shown in image 1).
I'm marking this thread Resolved, but with disappointment.