Range search

[sueadani]

I have one table with invoice amounts and want to search if the amount is found in table two - either the exact amount or any amount +- 50 to the original amount. 

[rubenmarin]

You can seach by range using >= and <=, like ">=100<=200"

[sueadani]

I need to use a join between tables so that I only bring from table2
records that have the same ID and invoice number as the record in table1and
that the sum of the invoice is equal to the sum of the invoice in table 1
or is close to the sum - plus or minus 50$

[rwunderlich]

Do you want to do this in script or in the UI?

-Rob

[sueadani]

In the script if possible

[rubenmarin]

Hi, in schemtics I think you can do:

1.- Load table 1

2.- Create mapping table with invoce and sum of amount.

3.- Load table 2 grouped by invoice and bring also the value of the mapping table. Reduce the table where the difference between both values are less than 50. 

4.- Load again table 2 using the invoices of the  3rd step (with exists(), keep, join) and join with the table loaded in step 1.

[ElisaF]

In reponse to @sueadani 

You can try the Interval Match function.

See the below script:

DATA1:
LOAD
ID&'#'&NoInvoice AS KEY,
SUM(Value) AS VALUE1
Group BY ID&'#'&NoInvoice
;
LOAD * INLINE [
ID, NoInvoice, Value
1, 101, 120
1, 101, 70
2, 102, 200
2, 104, 250
];

DATA2:
LOAD
*,
VALUE2-Floor(50) AS FromValue,
VALUE2+Floor(50) AS ToValue
;
LOAD
ID&'#'&NoInvoice AS KEY,
SUM(Value) AS VALUE2
Group BY ID&'#'&NoInvoice
;
LOAD * INLINE [
ID, NoInvoice, Value
1, 101, 120
2, 102, 190
];

Inner Join IntervalMatch (VALUE1, KEY)

LOAD FromValue, ToValue, KEY
Resident DATA2;

OUTPUT:

[sueadani]

Thank you!! I'll try when I get back to this task and see if I succeed 😀