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)
1 ACCEPTED SOLUTION

Accepted Solutions
Seven 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.

7 REPLIES
Six 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

 

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

Re: tMap Lookup Failing

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

[image: Inline image 2]
Seven Stars

Re: tMap Lookup Failing

You can trim in expression between lookup and expression.

 

 

Six Stars

Re: tMap Lookup Failing

Thanks @ashifa - 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"