Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
kwflng22
Contributor
Contributor

Compare Sum in two tables

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    
CustomerNamePODateInvoiceProductNumQty
A1/1/20191234544542
A1/1/20191234544543
A1/1/20191234578871
B1/1/20195443276561
B1/1/20195443276561
B1/1/20195443212121
D1/2/20194874478871
E1/3/20197676545542
A1/4/20196565378874

 

Table B    
CustomerDateInvoiceProdIDUnits
Alpha1/1/20191234544545
Alpha1/1/20191234578871
B1/2/20195443276562
Charlie1/2/20198667678871
Dept1/3/20194874478872
E1/4/20197676576561

 

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

CustomerNamePODateInvoiceProductNumSum Qty Table A Sum Qty Table B Difference
A1/1/2019123454454550
A1/1/2019123457887110
B1/1/2019544327656220
B1/1/2019544321212101
D1/2/201948744788712-1
E1/3/2019767654554202
A1/4/2019656537887404
1 Solution

Accepted Solutions
veidlburkhard
Creator III
Creator III

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

FinalTable.jpg

Hope this helps

Burkhard

View solution in original post

2 Replies
veidlburkhard
Creator III
Creator III

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

FinalTable.jpg

Hope this helps

Burkhard

kwflng22
Contributor
Contributor
Author

Hi Burkhard,

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