Compare Sum in two tables

[kwflng22]

Hello,

I was hoping to get some help from experienced users.  I have reviewed multiple posts and have been trying to get the difference between the sums of 2 fields on different tables, but cannot get the sum to reflect correctly for my scenario in any table I've tried.  An example of the data is in tables A and B below as well as the desired outcome.   The data will not be in spreadsheets, but it reflects what I'm working with.  While the data is similar on both tables, there is only 1 field that will have the same name and, for various reasons, there can be legitimate duplication of data in the files so row counts will not be the same.   I used keep function because was hoping to keep both tables, but am open to any suggestion that works.

Table A
CustomerName	PODate	Invoice	ProductNum	Qty
A	1/1/2019	12345	4454	2
A	1/1/2019	12345	4454	3
A	1/1/2019	12345	7887	1
B	1/1/2019	54432	7656	1
B	1/1/2019	54432	7656	1
B	1/1/2019	54432	1212	1
D	1/2/2019	48744	7887	1
E	1/3/2019	76765	4554	2
A	1/4/2019	65653	7887	4

Table B
Customer	Date	Invoice	ProdID	Units
Alpha	1/1/2019	12345	4454	5
Alpha	1/1/2019	12345	7887	1
B	1/2/2019	54432	7656	2
Charlie	1/2/2019	86676	7887	1
Dept	1/3/2019	48744	7887	2
E	1/4/2019	76765	7656	1

If the invoice is found on Table A, it should be captured on the final table regardless of if it was found on Table B

Final Table

CustomerName	PODate	Invoice	ProductNum	Sum Qty Table A	Sum Qty Table B	Difference
A	1/1/2019	12345	4454	5	5	0
A	1/1/2019	12345	7887	1	1	0
B	1/1/2019	54432	7656	2	2	0
B	1/1/2019	54432	1212	1	0	1
D	1/2/2019	48744	7887	1	2	-1
E	1/3/2019	76765	4554	2	0	2
A	1/4/2019	65653	7887	4	0	4

[veidlburkhard · Accepted solution]

Hi Kwfing22,

my suggestion would be to do it like this:

the script

A1:
LOAD * INLINE [
CustomerName, PODate, Invoice, ProductNum, Qty
A, 01.01.2019, 12345, 4454, 2
A, 01.01.2019, 12345, 4454, 3
A, 01.01.2019, 12345, 7887, 1
B, 01.01.2019, 54432, 7656, 1
B, 01.01.2019, 54432, 7656, 1
B, 01.01.2019, 54432, 1212, 1
D, 01.02.2019, 48744, 7887, 1
E, 01.03.2019, 76765, 4554, 2
A, 01.04.2019, 65653, 7887, 4
];

//Eliminate the dublicatione:
NoConcatenate
A2:
LOAD
CustomerName,
PODate,
Invoice,
Invoice&'|'&ProductNum as InvoiceID,
Sum(Qty) as Qty
Resident A1
Group By CustomerName,
PODate,
Invoice,
ProductNum
;

DROP Table A1;

B1:
LOAD *
Where Exists(InvoiceID); //Load Invoice and ProdID which exists in Table A2, only!
LOAD Customer, Date, Invoice&'|'&ProdID as InvoiceID, Units;
LOAD * INLINE [
Customer, Date, Invoice, ProdID, Units
Alpha, 01.01.2019, 12345, 4454, 5
Alpha, 01.01.2019, 12345, 7887, 1
B, 01.02.2019, 54432, 7656, 2
Charlie, 01.02.2019, 86676, 7887, 1
Dept, 01.03.2019, 48744, 7887, 2
E, 01.04.2019, 76765, 7656, 1
]
;
//Building the result table with field 'Units' from B1:
Join(A2)
LOAD
InvoiceID,
Units
Resident B1
;
DROP Table B1;

The final Table

Hope this helps

Burkhard

[kwflng22]

Hi Burkhard,

I used your structure with a couple of tweaks and was able to get the results I needed.  Thank you very much!!!