topic Re: Fuzzy/Close Name Match in QlikView

Fuzzy/Close Name Match

Anonymous — Thu, 28 Apr 2016 09:17:23 GMT

Hi,

I am not able to get to a very important concept of fuzzy/close match in QV. Not sure if it has the capability of that.

As an example here is what I am looking to do. I have 2 name fields in 2 different files. Employee Name in Employee file and Vendor Name in Vendor file. I need to run a search to see if any of the employees are acting as vendors, this can't be done using exact join as there could be a small variation in the name, usually a letter or two.

Like matching Lynne to Lynn, John Smithe to Jon Smith.

Please advise

Thanks

Sunny

Re: Fuzzy/Close Name Match

ajsjoshua — Thu, 28 Apr 2016 09:21:34 GMT

Hi,

pls post a sample in excel.

Re: Fuzzy/Close Name Match

Thu, 28 Apr 2016 09:26:26 GMT

Please go through below link,

Re: Fuzzy Search in Scripts

Re: Fuzzy/Close Name Match

marcus_sommer — Thu, 28 Apr 2016 09:48:07 GMT

Useful might be here Please help me get the string pattern of each field. the answers from Marco.

- Marcus

Re: Fuzzy/Close Name Match

Anonymous — Fri, 29 Apr 2016 04:05:30 GMT

attached

Re: Fuzzy/Close Name Match

Anonymous — Fri, 29 Apr 2016 04:06:14 GMT

attached sample files for reference

Re: Fuzzy/Close Name Match

ajsjoshua — Fri, 29 Apr 2016 04:37:05 GMT

hi,

check the attachment.

Steps:

Try following below steps:

1) LOAD the tables.

2) Create a Straight table, with empnamae and vendorname as dimensions.

3) use below expression

=WildMatch([Emp Name],mid([vendor name],1,1)&'*'& mid([vendor name],2,1)&'*' & mid([vendor name],3,1)&'*')

Regards,

Joshua.

Re: Fuzzy/Close Name Match

jonathandienst — Fri, 29 Apr 2016 05:25:11 GMT

This may help.

Rob Wunderlich's cookbook has an example doing a fuzzy mapping.

http://robwunderlich.com/Download.html

Re: Fuzzy/Close Name Match

Anonymous — Fri, 29 Apr 2016 05:26:39 GMT

Thanks Joshua, it works however that example works well for a sample data of a few records. How can I do that if I have thousands of rows to match with each other.

Re: Fuzzy/Close Name Match

ajsjoshua — Fri, 29 Apr 2016 05:31:44 GMT

Hi,

Hope it will work fine for 1000 records also,

if possible you share me 1000 records sample,i will try and let you know.

Regards,

Joshua.

Re: Fuzzy/Close Name Match

Anonymous — Fri, 29 Apr 2016 05:32:37 GMT

Hi Josh, I am getting the following msg at that link and when I go to qlikviewcookbook.com, I see a lot of enteries but not the one we want

All downloads are now served from the Tools and Recipes sections at:

QlikviewCookbook.com

Go there to access downloads and updated content

This directory does not yet contain any files.

Re: Fuzzy/Close Name Match

arieidel — Mon, 02 May 2016 19:13:52 GMT

Hello Sunny,

You can find that mapping example in the Recipe section. Direct link to download the example: http://qlikviewcookbook.com/download/mapping-substrings-2/

Anyway, I have the same need as yours but this example doesn't help me. I think it's easier when the fuzzy search uses single words but when the string to be search is a sentence -like a name and a lastname- there isn't a straightforward solution...

But if you find it, please share!

Regards,

Ariel

Re: Fuzzy/Close Name Match

MarcoWedel — Mon, 02 May 2016 21:22:23 GMT

Hi,

one solution to match similar names could be to calculate the levenshtein distance (Levenshtein Algorithm) and apply some treshold to it:

tabEmployees:
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1029287-223516/Employee%20file.xlsx] (ooxml, embedded labels, table is Sheet1);

tabVendors:
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1029287-223515/vendors.xlsx] (ooxml, embedded labels, table is Sheet1);

tabLink:
LOAD Distinct [Emp Name] Resident tabEmployees;
Join
LOAD Distinct [vendor name] Resident tabVendors;
Join
LOAD [Emp Name], [vendor name], levenshtein([Emp Name], [vendor name]) as LevDist Resident tabLink;
Right Join
LOAD Distinct LevDist Resident tabLink Where LevDist<=2;

hope this helps

regards

Marco

Re: Fuzzy/Close Name Match

Anonymous — Mon, 09 May 2016 06:47:27 GMT

Thanks Marco,

How are you getting the function levenshtein in the script? I tried but this function does not exist.

Re: Fuzzy/Close Name Match

settu_periasamy — Mon, 09 May 2016 07:05:00 GMT

Hi,

levenshtein is a User Defined Function in the Macro Editor. Click ( Ctrl + M ), you can find it..

Levenshtein Algorithm

Re: Fuzzy/Close Name Match

Anonymous — Mon, 09 May 2016 07:07:34 GMT

Thanks settu, can you please attach the qvw, I don't have that

Re: Fuzzy/Close Name Match

settu_periasamy — Mon, 09 May 2016 07:13:37 GMT

I just showed it from marcowedel‌attachment. You can download it.. Or You can just the mentioned link.

Re: Fuzzy/Close Name Match

Anonymous — Mon, 09 May 2016 08:15:42 GMT

Thanks I missed that

Re: Fuzzy/Close Name Match

Anonymous — Mon, 09 May 2016 08:15:54 GMT

Thanks Marco

Re: Fuzzy/Close Name Match

Anonymous — Mon, 09 May 2016 08:23:51 GMT

Hi Marco,

I just added a few non matching exceptions also in the file and those came too in the table box. Any way we could avoid that to appear in the table box.

Emp no	Emp Name	vendor name	vendor num
12	dell	de	2
999	genpact
111	John Smithe	Jon Smith	88888
122	Lynne	Lynn	98878
233	Mike Pepper	Mike Peper	99876
		gen	1
		o	3
		p	4
		q	5
		r	6
		s	7