Multiple Fuzzy Lookup

Is it possbile to have multiple fuzzy lookup within a data flow?

I need to have at least 3 fuzzy lookup in a data flow. Here're the conditions that I try to find match: 1=Zip&City, 2=Zip&State, 3=City&State. I've the first fuzzy lookup working fine. After that, I've a conditional split to get any unmatch, then use another fuzzy lookup for a second condition...at this point, I get the error saying "The package contains two objects with duplicate name of output column _Similarity..." I do not need to get the _Similarity and _Confidence, so is there a way to exclude them from returning in the output?

Any comments?

Thanks in advance.

Ash,

Using the Advanced Editor for the Fuzzy Lookup task, you can change the name of the _Similarity and _Confidence fields. This would not exclude them from the data flow, but would solve the duplicate name issue.

Frank

|||

Frank,

I do not find any options to re-name the _Similarity and _Confidence fields from the Advanced tab of the Fuzzy Lookup Transformation Editor. Can you please be more specific?

Thank you for the comment.

|||

On the 'Input and Output properties' tab -'Fuzzy Lookup Output'-'Output Columns'

Use the 'Name' property to rename the fields.

Frank

|||Thanks Frank. That's exactly what I was looking for.

Multiple FKs of the same PK problem!

Hi SQL folks,

I have a fuzzy problem with my application,

I'm building a small internal messaging system. I have two tables: SysUsers and mail tables.

I need to select both the "mailFrom" and "mailTo" from the mail table - both of them is a uniqueidenifier and are FK from the UserId in the SysUsers table- , and then get the correspoding UserName from the SysUsers table, how can I do that?

Here is my query:

/*Get the UserName of both the Sender and Reciever*/
SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo, SysUsers.UserName
FROM mail INNER JOIN
SysUsers ON mail.mailTo= SysUsers.UserId
AND mail.mailFrom= SysUsers.UserId

/*Get only the current users mails*/
WHERE (SysUsers.UserId = @.UserId)

Hello Jocker,

You have to define two inner join to the same table with different aliasses.

SELECT ... , MailTo.UserName, MailFrom.UserName

FROM mail

INNER JOIN SysUsers AS MailTo ON MailTo.UserId = mail.mailTo

INNER JOIN SysUsers AS MailFrom ON MailFrom.UserId = mail.mailFrom

WHERE (MailTo.UserId = @.UserId OR MailFrom.UserId = @.UserId)

|||

You need to alias the Sysusers Table

SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo,
S1.UserName AS UserNameTo,S2.UserName AS UserNameFrom
FROM mail
INNER JOIN SysUsers S1 ON mail.mailTo = S1.UserId
INNER JOIN SysUsers S2 ON mail.mailFrom = S2.UserId

WHERE (S1.UserId = @.UserId)

|||

jeroenm:

Hello Jocker,

You have to define two inner join to the same table with different aliasses.

SELECT ... , MailTo.UserName, MailFrom.UserName

FROM mail

INNER JOIN SysUsers AS MailTo ON MailTo.UserId = mail.mailTo

INNER JOIN SysUsers AS MailFrom ON MailFrom.UserId = mail.mailFrom

WHERE (MailTo.UserId = @.UserId OR MailFrom.UserId = @.UserId)

Hi jepenm,

Thanks for you responce :)

|||

TATWORTH:

You need to alias the Sysusers Table

SELECT mail.messageId, mail.messageSubject, mail.mailFrom, mail.mailTo,
S1.UserName AS UserNameTo,S2.UserName AS UserNameFrom
FROM mail
INNER JOIN SysUsers S1 ON mail.mailTo = S1.UserId
INNER JOIN SysUsers S2 ON mail.mailFrom = S2.UserId

WHERE (S1.UserId = @.UserId)

Hi TATWORTH,

Thanks for your response