Duplicate Invoices within tolerance

[#1]

Hi everyone,

I have a small issue that's really beginning to bug me.

I have a database with say the following columns:

- InvoiceValue
- InvoiceDate

- InvoiceNumber

What I am trying to do is create a straight table that looks for duplicate invoices where the invoice number is the same (easy) but also the invoice value is within 0.01p (tolerance). I can't seem to get the expression correct for the 0.01p part.

I think this is most probably really simple, but I just can't get it right.

Many thanks in advance

[sbaldwin]

Hi, the best thing would be to add in a flag to the load script, but assuming you need to have this dynamically based on other selections have you tried loading a copy table joining on the invoice number then comparing the values in the two tables? i've attached an example.

Thanks

Steve

[#3]

Hi Steve,

Many thanks for that, but it didn't seem to work correctly. I just seem to just get duplicate invoice numbers, the value seems to be unaffected if that makes sense.

[#4]

Thanks for the reply.

This won't work unfortunately because InvoiceValue1 and InvoiceValue2 are actually from the same column e.g. InvoiceValue only.

Basically I am looking for something like this:

IF(count(InvoiceNumber > 1, Compare InvoiceValue against other(s) InvoiceValue where the InvoiceValue is 0.01p different, show the Invoice Number, else ignore.

I hope that makes sense