Sum Credit/Debit if matching condition met

[jenmclean]

In this chart, I need to combine (calculate) for a net total if the number in the RMA/PO# column and the Vendor Inv column match. This is an accounts payable function. So on the same check number there in an invoice and a debit and I want to combine these two lines for a Pmt Amt that would equal $240.42 as the information from this chart is exported into the accounting software. This would eliminate repetitive tasks for the employee.

How would I write an expression that would do this?

Message was edited by: Jennie Elliott I have added an example of what my end results should be Attached is what I am trying to achieve: If RMA/PO# = Vendor Inv, then Sum for a Net Payment (Invoice - Credit). I need to combine these matching lines into one line if those numbers match. Do I script this or write an expression?

[jenmclean]

I have added a spreadsheet example to my original post.

[jenmclean]

QVD script:

VendorInvoice_Child:
NoConcatenate LOAD Distinct
VND_ChildTransID,
VND_TransID,
VND_Amount,
VND_ChildAmtFlagZero,
VND_ChildDueDate,
VND_TransDate,
VND_TRAPO,
VND_VendorPO,
VND_VendorInv,
if(VND_Amount<0, VND_Amount) as VND_Credit,
if(VND_Amount>0, VND_Amount) as VND_Payment

FROM

(qvd)
WHERE(VND_VendorInv <> 'CLEAR' And VND_TransDate >= '1/1/2015' and VND_ChildDueDate >= '1/1/2015');

Store * FROM VendorInvoice_Child into C:\QlikView\QlikView Production\QVDocuments\SourceDocuments\QVD\TEST\VendorInvoice_CHILD_FINAL.qvd (qvd);
Drop Table VendorInvoice_Child;

[ramoncova06]

based on your file, separate the RMA from the Vendor and then do a left join and add the amounts up

LOAD [Date Entered],

     [Acct #],

     [Entered By],

     Vendor,

     [Check #],

     [Str #],

     [Due Date],

     [Vendor Inv],

     [Pmt Amt] as [Vendor AMT],

     [Vendor Inv] as ID

FROM

(ooxml, embedded labels, table is Sheet2)

WHERE [Vendor Inv] > 0;

!

LEFT JOIN

LOAD [Date Entered],

     [Acct #],

     [Check #],

     [Str #],

    // [Due Date], Not needed since the vendor is being used

     [RMA/PO #] ,

     [RMA/PO #] as ID,

     [Pmt Amt] as [RMA/PO # AMT]

FROM

(ooxml, embedded labels, table is Sheet2)

WHERE [RMA/PO #] > 0;