wildmatch data from two tables

[ziabobaz]

Hi!

I have two tables with addresses.

I want to wildmatch field values form the Clean table into the Source table, and if wildmatch is true, I want to add the value from the Clean table. It is the fuzzy lookup equivalent to Excel (which does not exist in QS)

Please, see screenshot below

Thank you!

[sunny_talwar]

You can use MapSubString() function to do this. Would you be able to provide above data in text format to show how it can be done?

[ziabobaz]

Sunny, sure, thank you

Although I did try it. The problem is that in my real data the street names contain numbers or other names that match (like "street","downtown")

'1 Brando street', '2 Brando street', etc.

So the function takes each number and matches it against each other - I get mass.

CleanTable:		SourceTable:		ResultTable:
CleanAddress		SourceAddress		SourceAddress	CleanAddress
abc		fdf abcfdf str.		fdf abcfdf str.	abc
def		street abc		street abc	abc
		fsdfds fabcgf		fsdfds fabcgf	abc

[sunny_talwar · Accepted solution]

Try this

CleanTable:
Mapping
LOAD CleanAddress,
	 '/' & CleanAddress & '\';
LOAD * INLINE [
    CleanAddress
    abc
    def
];

SourceTable:
LOAD SourceAddress,
	 TextBetween(MapSubString('CleanTable', SourceAddress), '/', '\') as CleanAddress;
LOAD * INLINE [
    SourceAddress
    fdf abc fdf str.
    street abc
    fsffds fabcgf
];

[ziabobaz]

don;t know why but '/' '\' reduced number of errors dramatically

as for the numbers - I will remove it from the street names

thank you