Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 ];
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?
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 |
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 ];
don;t know why but '/' '\' reduced number of errors dramatically
as for the numbers - I will remove it from the street names
thank you