topic Re: Sum Credit/Debit if matching condition met in QlikView

Sum Credit/Debit if matching condition met

jenmclean — Wed, 25 Mar 2015 18:33:26 GMT

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?

Re: Sum Credit/Debit if matching condition met

JonnyPoole — Wed, 25 Mar 2015 19:42:15 GMT

Hi - I loaded a new 'ID' field that takes its value from the RMA/PO # or the Vendor Inv field depending which is null().

Data:

LOAD Check,

Date,

RMA,

[Vendor Inv],

if( isnull(RMA),[Vendor Inv],RMA) as ID,

FROM

(ooxml, embedded labels, table is Sheet1);

ID:

Load

Distinct

resident Data;

Then i created a straight table with ID as a dimension (but hidden per presentation tab settings).

It gave me this for attached data sample.

Does this help ?

Re: Sum Credit/Debit if matching condition met

jenmclean — Wed, 25 Mar 2015 19:51:09 GMT

I'll try that and let you know.

Thanks!

Re: Sum Credit/Debit if matching condition met

petter — Wed, 25 Mar 2015 20:06:15 GMT

Here is a variant using a calculated dimension (could be done in the load script thus removing the need for a calculated dimension), a pivot table, and partial sum on the [Date Entered] field. The expression is simply

Sum([Pmt Amt]). Have attached a sample app too.

The first table is just a Table Box to illustrate the raw table. The second table is a pivot with all dimensions expanded. The third table is just a copy of the second to show how it looks like when you "Collapse All" on the first dimension giving you hopefully exactly what you want your users to export to Excel...

Re: Sum Credit/Debit if matching condition met

jenmclean — Wed, 25 Mar 2015 21:12:28 GMT

I cant' put it in a Pivot Table due to having to export all data into Great Plains accounting software. It must be in a straight table chart.

Re: Sum Credit/Debit if matching condition met

petter — Wed, 25 Mar 2015 21:15:21 GMT

Neither Export or pressing the XL icon on the title bar on the chart should work fine for Pivot Table... Can you explain in more detail?

Re: Sum Credit/Debit if matching condition met

petter — Wed, 25 Mar 2015 21:16:01 GMT

I mean "Both ... and ...." not Neither ....

Re: Sum Credit/Debit if matching condition met

petter — Wed, 25 Mar 2015 21:35:40 GMT

You can even automate the export from a button and calling a very simple Macro (which will run perfectly well

even on a QlikView server).

Re: Sum Credit/Debit if matching condition met

petter — Wed, 25 Mar 2015 21:53:56 GMT

Or if you prefer to do it in a load script so you actually get two new fields you can even do the Export via an Action/External/Export which demands to have fields with values to be able to export - I have also attached the lates QVW with the code/app:

Re: Sum Credit/Debit if matching condition met

jenmclean — Wed, 25 Mar 2015 21:56:51 GMT

This might work better, I will try it out!

Thanks!

Re: Sum Credit/Debit if matching condition met

jenmclean — Thu, 26 Mar 2015 17:18:47 GMT

I have added a spreadsheet example to my original post.

Re: Sum Credit/Debit if matching condition met

jenmclean — Thu, 26 Mar 2015 17:33:32 GMT

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;

Re: Sum Credit/Debit if matching condition met

ramoncova06 — Thu, 26 Mar 2015 17:43:20 GMT

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;