Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
karan_kn
Creator II
Creator II

Fuzzy Matching Names and Join table in Load Statment

We have to match two lists that only have Company names, and no common IDs or any other field to JOIN them on. To use fuzzy logic to match Company name fields to join the table in Load Statment. Please refer to the file and screenshot.

fuzzy.JPG

 

2 Replies
Saravanan_Desingh

Please check the below link also. This is my solution similar to your problem. If you have any question on this, let me know.

 

https://community.qlik.com/t5/New-to-QlikView/Join-table-without-prefix/m-p/1698575/highlight/false#...

Function levenshtein( a, b )
	Dim i,j,cost,d,min1,min2,min3
	
 ' Avoid calculations where there there are empty words
	If Len( a ) = 0 Then levenshtein = Len( b 😞 Exit Function
	If Len( b ) = 0 Then levenshtein = Len( a 😞 Exit Function
	
 ' Array initialization	
	ReDim d( Len( a ), Len( b ) )

	For i = 0 To Len( a 😞 d( i, 0 ) = i: Next
	For j = 0 To Len( b 😞 d( 0, j ) = j: Next
	
 ' Actual calculation
	For i = 1 To Len( a )
		For j = 1 To Len( b )
                        If Mid(a, i, 1) = Mid(b, j, 1) Then cost = 0 Else cost = 1 End If
			
			' Since min() function is not a part of VBScript, we'll "emulate" it below
			min1 = ( d( i - 1, j ) + 1 )
			min2 = ( d( i, j - 1 ) + 1 )
			min3 = ( d( i - 1, j - 1 ) + cost )
			
			If min1 <= min2 And min1 <= min3 Then
				d( i, j ) = min1
			ElseIf min2 <= min1 And min2 <= min3 Then
				d( i, j ) = min2
			Else
				d( i, j ) = min3
			End If
		Next
	Next

	levenshtein = d( Len( a ), Len( b ) )
End Function
Table1:
LOAD * INLINE [
    ContID, Contact, CompanyName
    32445, John@decisive.org, Decisive Data
    43634, Dave@decisive.org, Decisive Data Inc
    234234, Mike@decisive.org, Decisive Data Inc.
    342348, Rita@datcom.com, Data com
    45686, Caleb@datcom.com, Datacom
    65896, Jane@agregado.com, "Agregado, SA de CV"
    189466, Derek@agregado.com, Agregado sa de cv
    45663, Mave@arrow.uk, Arrow
    56862, Kris@nypb.com, NY Personnel Bd
    98563, Steve@nypb.com, NY Personnel Board
];

Join
Table2:
LOAD * INLINE [
    CompanyID, CompanyName2
    C23001, "Decisive Data, Inc."
    C23002, Data com LLC
    C23003, "Agregado, S.A. de C.V."
    C23004, Arrow AB
    C23005, NY Personnel Board
];

tabOut:
LOAD *, Levenshtein(CompanyName, CompanyName2) As LevenDist
Resident Table1;

Drop Table Table1;

commQV37.PNG

Brett_Bleess
Former Employee
Former Employee

The following Design Blog post may provide another alternative too:

https://community.qlik.com/t5/Qlik-Design-Blog/Mapping-and-not-the-geographical-kind/ba-p/1463192

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.