topic Re: Duplicate Invoices within tolerance in QlikView

Duplicate Invoices within tolerance

Thu, 24 Jan 2013 09:52:18 GMT

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

Re: Duplicate Invoices within tolerance

sbaldwin — Thu, 24 Jan 2013 10:41:18 GMT

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

Re: Duplicate Invoices within tolerance

Thu, 24 Jan 2013 13:19:40 GMT

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.

Re: Duplicate Invoices within tolerance

Thu, 24 Jan 2013 13:25:00 GMT

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