topic Re: tMap Lookup Failing in Talend Studio

tMap Lookup Failing

Anonymous — Sat, 16 Nov 2024 09:13:44 GMT

I have a CSV file with a Column that contains a string. I'm trying to lookup that string against a lookup table in SQL and get a resulting ID.

CSV

Area,Client,Camera

ABC,109 Forest,123

ABC,109 Lancaster,123

SQL

ID,ClientName

1,109 Forest

2, 109 Lancaster

Desired Output

ABC, 1, 123

ABC, 2, 123

Current Output

ABC,0,123

I have created a "Left Outer Join" between CSV.Client and SQL.ClientName and my output Schema is:

CSV.Area

SQL.ID

CSV.Camera

Clearly, I'm not getting a match between the CSV.Client field and the SQL.ClientName field, but I can't fathom why - they are identical in case, and no trailing spaces, etc.

I'd welcome any suggestions.

Re: tMap Lookup Failing

SachinD — Tue, 03 Oct 2017 10:41:05 GMT

Hi,

remove space between 2, 109 Lancaster in SQL 2nd row

SQL

ID,ClientName

1,109 Forest

2,109 Lancaster

o/p

Desired Output

ABC, 1, 123

ABC, 2, 123

Re: tMap Lookup Failing

ashif2 — Tue, 03 Oct 2017 10:45:17 GMT

If you are data is not formatted correctly , trim the data with the help of trim option available in input component.

Re: tMap Lookup Failing

Anonymous — Tue, 03 Oct 2017 10:56:13 GMT

Not sure what I'm doing wrong.

--------------+--------------------------+--+---------.
| tLogRow_1 |
|=-------------+--------------------------+--+--------=|
|Area |Client |ID|Camera_ID|
|=-------------+--------------------------+--+--------=|
|Athol |109 Forest |0 |100530 |
|Athol |109 Forest |0 |100531 |
|Athol |109 Forest |0 |100532 |

CSV

Area,Client,Camera ID,Pole Number,Location/Street name,Camera name,Camera Type,Analogue or IP,Camera IP address,Server IP,Software,Status,

Athol,109 Forest,100530,No pole,Camera 1,Bullet camera,Overview,Analogue,,,iSentry,Monitored,
Athol,109 Forest,100531,No pole,Camera 2,Bullet camera,Overview,Analogue,,,iSentry,Monitored,
Athol,109 Forest,100532,No pole,Camera 3,Bullet camera,Overview,Analogue,,,iSentry,Monitored,
Athol,109 Forest,100533,No pole,Camera 4,Bullet camera,Overview,Analogue,,,iSentry,Monitored,

SELECT [ID],
[ClientName]
FROM [dbo].[Client];

flow.png
map.png

Re: tMap Lookup Failing

Anonymous — Tue, 03 Oct 2017 11:25:39 GMT

I had to trim the input from the SQL Table (I checked "trim all string columns", it looks like it was producing space-padded values. Is there a way to trim the lookup column in the tMap, or can I only do it in the database input source component?

Re: tMap Lookup Failing

ashif2 — Tue, 03 Oct 2017 11:35:28 GMT

You can trim in the expression between input and look-up

[image: Inline image 2]

Re: tMap Lookup Failing

ashif2 — Tue, 03 Oct 2017 11:36:41 GMT

You can trim in expression between lookup and expression.

tMapExpression.JPG

Re: tMap Lookup Failing

Anonymous — Tue, 03 Oct 2017 11:40:05 GMT

Thanks @ashif - how would I turn that expression into

StringHandling.TRIM(row1.Dept)=StringHandling.TRIM(row2.Dept)

I can only see how to add expressions to the the expression key not to the column being "looked up against"