Trying to find the 'best match' for numbers inc. Zeros 2

[Welshbird]

Hi guys - I'm needing some help with a query, and I've reached the 'no ideas anymore' stage!

I'm performing a kind of 'fuzzy' match in order to find the most similar record in one table to a record in another table.

There are several fields to be compared, and some of them are deemed more important to match than others, so I have another wee small table containing weighting scores.

Some of the matching fields are text, and these have to match exactly or not at all, so these are easy (if are = then 1 * weighting score, else 0), and the rest are numeric fields and I'm just trying to find the closest.

Because I need to allow for the weighting score, I was aiming to divide the smallest number by the biggest, then if they match I'll get 1, and if not I'll get a fraction which is smaller than 1; this can then be multiplied by the weighting score again.

So far so good! I've built a 'model' of this is Excel so I can see if working and in general I'm really happy.

It has thrown up an issue that I hadn't thought of though...

Some of the numbers to be matched may well be zero.

This is perfectly valid, but doesn't help if I'm trying to get close to a 1 by dividing one by the other.

So, any ideas about how I calculate how close a number is, ending up with a maximum of 1, if one of them can be zero??

Thanks in advance for any suggestions at all.

Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[Stella740pl]

Yes, I already noticed the function, and that's what I mean. Maximum is a function, and you need to calculate the "closeness coefficient" for the whole table, right? So (if the table is large enough, and dynamic, and not completely locked for the process) can it happen that the maximum changed while only part of the table is processed, so the other part will already be calculated with a different maximum?

 

[Welshbird]

The table is not dynamic, so for me this is not an issue.

I have no idea how I would deal with it if it were actually!

The purposes of this actually mean its better this way, as it gives some stability to the data at the end of it all.

Fee.

"The question should be, is it worth trying to do, not can it be done"

 

[Stella740pl]

The table is not dynamic, so for me this is not an issue.

I thought from your previous post that it is.
If it is not an issue, then you have got a good solution.

 

[Welshbird]

Thanks Stella!

I've built this into the middle two nested loops to trawl through the two tables.

I'm trying to just output the record when I get the maximum score for the record in the inner loop, rather than all of them, as I've done 'all of them' to test, and I don;t need them in the working version. I haven't quite got that working yet.... but I'm getting there I think!



Fee.

"The question should be, is it worth trying to do, not can it be done"