tMap Lookup Failing

[Anonymous]

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.

[SachinD]

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

[ashif2 · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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?

[ashif2]

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

[image: Inline image 2]

[ashif2]

You can trim in expression between lookup and expression.

tMapExpression.JPG

[Anonymous]

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"