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: 
hobanwashburne
Creator
Creator

Percentage match of the same field in two records

Does QlikView have a function that can compare the values in fields and return a percentage match?

For example:

Row    Name           City           State     Zip  

1      ABC Company    Walla Walla    WA        99362  

2      ABC Co         Walla Walla    WA        99362 

3      The ABC Comp   Walla Walla    WA        99362

4      Dave           Indianapolis   IN        46060

5      ABC Comp       Walla Walla    WA        99362

Rows 1, 2, 3, & 5 are most likely the same company while row 4 is not.

4 Replies
sunny_talwar

May be see if this helps

Approximate String Matching in QlikView

Anonymous
Not applicable

I think to get a percentage match you would need to have a base standard to compare against. Comparing 2 fields would be easier but with multiple how does it know which one to create a percentage match from.

So if  "THE ABC COMPANY" was the standard then you could write a script to check against that

Or
MVP
MVP

You've actually got two different problems here:

1) You aren't trying to compare values in fields, you're trying to compare values between records. Each record would have to be compared not only to every other record individually, but you would need to somehow group all of the matching records (which have different similarity scores between any two nodes) into one group that says "Lines 1, 2, 3, and 5 are very similar".

2) Actually calculating the matching level of the values, possibly across multiple columns (though you can work around this by concatenating the columns into one long string and comparing those).

There was a good post a while back about calculating the similarity of two text strings, but I don't think it will apply in this case - you're probably going to have to write an actual program (or find one that someone else has written) to get this done, I think, or use a clustering algorithm in software that supports it (such as R, which also integrates with QlikView). Perhaps someone else will have a QlikView approach that works for generating group similarity, but I can't think of one.

Fuzzy Matching/Joining