4 Replies Latest reply: Nov 14, 2017 8:26 AM by Or Shoham

# 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.

• ###### Re: Percentage match of the same field in two records

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

• ###### Re: Percentage match of the same field in two records

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