tMap Lookup Failing

Six Stars

tMap Lookup Failing

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

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.

Tags (2)

Accepted Solutions
Eight Stars

Re: tMap Lookup Failing

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


All Replies
Seven Stars

Re: tMap Lookup Failing

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

 


Capture1.JPGCapture2.JPG

 

Eight Stars

Re: tMap Lookup Failing

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

Six Stars

Re: tMap Lookup Failing

Not sure what I'm doing wrong.

 

flow.pngmap.png

 

--------------+--------------------------+--+---------.
| 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];

client.png

 

Six Stars

Re: tMap Lookup Failing

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?
Eight Stars

Re: tMap Lookup Failing

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

[image: Inline image 2]
Eight Stars

Re: tMap Lookup Failing

You can trim in expression between lookup and expression.

 

 

Six Stars

Re: tMap Lookup Failing

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"

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads