Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
Hi Burkhard,
I used your structure with a couple of tweaks and was able to get the results I needed. Thank you very much!!!