Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
ziabobaz
Contributor III

wildmatch data from two tables

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 belowScreenshot_1.jpg

Thank you!

Labels (2)
1 Solution

Accepted Solutions

Re: wildmatch data from two tables

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
];
4 Replies

Re: wildmatch data from two tables

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
Contributor III

Re: wildmatch data from two tables

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 SourceAddressCleanAddress
abc fdf abcfdf str. fdf abcfdf str.abc
def street abc street abcabc
  fsdfds fabcgf fsdfds fabcgfabc

 

Re: wildmatch data from two tables

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
Contributor III

Re: wildmatch data from two tables

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

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

thank you