Help Needed On : knock off Debit With Credit on Script

[#1]

Dear All,

I have a table in which customer related Debit : Invoice and Credit : Payment Recd. entries stored. I just want to knock off debit entries with credit entries on FIFO basis for each customer to calculate Payment Relization on script level.

Please help me in this.

trnTabale:

rowId	trnNo	trnDate	custId	trnType	trnAmount	trnAmt	trnDrAmt	trnCrAmt
1	BNK-1	01/05/2011	a1	Cr	50000	50000	-	50000
2	INV-1	05/05/2011	a1	Dr	70000	-70000	70000	-
3	BNK-2	05/05/2011	a1	Cr	15000	15000	-	15000
4	INV-2	06/05/2011	a2	Dr	40000	-40000	40000	-
5	INV-3	07/05/2011	a1	Dr	25000	-25000	25000	-
6	BNK-3	08/05/2011	a1	Cr	15000	15000	-	15000

ResultTable:

custId	Inv. No.	Date	Amount	RecdAmt	RecdOn
a1	INV-1	05/05/2011	70000	70000	08/05/2011
a1	INV-3	07/05/2011	25000	10000	08/05/2011
a2	INV-2	06/05/2011	40000	0	-

Thanks In Advance

Regards

IndianQvLover

[robert99]

I tried another approach based on the manual

24.4 Usng the extended inteRval match syntax to resolve slowly changing dimension problems

Where invoices are loaded and then receipts amd then interval match is used to match receipts with invoices

and on this http://community.qlik.com/thread/17759

I have changed your figures slightly to see if it work for more than one paying customer

Hopefully it works for larger quantities

custId	Inv Num	Inv Date	SumInv	Paid	FIFO fully Paid Date	TN2	Total Invoiced	TotalPaid
a1	INV-1	05/05/2011	45000	45000	05/05/2011	BNK-2	92000	91000
a1	INV-3	07/05/2011	12000	12000	09/05/2011	BNK-3	92000	91000
a1	INV-4	08/05/2011	35000	34000	-	-	92000	91000
a2	INV-2	06/05/2011	40000	40000	20/06/2011	BNK-5	40000	52000

[robert99]

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

set NullInterpret = '';

Data:

load * Inline [

rowId, trnNo, trnDate, custId, trnType, trnAmount, trnAmt, trnDrAmt, trnCrAmt

1, BNK-1, 01/05/2011, a1, Cr, 31000, 31000, 0, 31000

2, INV-1, 05/05/2011, a1, Dr, 45000,-45000,45000, 0

3, BNK-2, 05/05/2011, a1, Cr, 15000, 15000,0, 15000

4, INV-2, 06/05/2011, a2, Dr, 40000,-40000,40000, 0

5, INV-3, 07/05/2011, a1, Dr, 12000, -12000,12000,0

6, INV-4, 08/05/2011, a1, Dr, 35000,-35000,35000, 0

7, BNK-3, 09/05/2011, a1, Cr, 45000, 45000,0, 45000

8, BNK-4, 20/05/2011, a2, Cr, 20000, 20000,0, 20000

9, BNK-5, 20/06/2011, a2, Cr, 21000, 21000,0, 21000

10, BNK-6, 28/06/2011, a2, Cr, 11000, 11000,0, 11000

];

//  load invoices

DataInv:

LOAD 

    rowId,

    trnNo,

    trnDate,

    custId,

    trnType,

    trnAmount,

    trnAmt,

    trnDrAmt,

    IF(PREVIOUS(custId)=custId,

    PEEK('AccInv'),0)+trnAmount AS AccInv,

    trnCrAmt

resident Data

where trnType = 'Dr'

order by trnType,custId,trnNo,rowId

;

// load receipts NB all these fields are not needed

NULLASVALUE AccumPaid,AccumPaidOpen;

DataPaid:

LOAD

rowId as RID2, 

    trnNo as TN2,

    trnDate as PaidDate,

    custId ,

    trnType AS TT2,

    trnAmount AS PaidAmt2,

    trnAmt as TA3,

    trnDrAmt AS  TDR2,

    IF(PREVIOUS(custId)=custId,PEEK('AccumPaid'),0)+trnAmt AS AccumPaid,

    trnCrAmt AS trct2

resident Data

where trnType = 'Cr'

order by trnType,custId,trnNo,trnDate

;

// TO CALCULATE THE RANGE TO DO THE INTERVAL MATCH

DataPaidAdd:

left join

load

RID2,

IF(PREVIOUS(custId)=custId,Previous (AccumPaid)+.01,0.1) AS AccumPaidOpen

resident DataPaid;

// TO CALCULATE THE TOTAL PAID BY CUSTOMER

PaidTotal:

left join

load

custId,

Sum (PaidAmt2) as TotalPaid

resident DataPaid

group by custId;

drop table Data;

// TO MATCH RECEIPTS TO INVOICES BY CUSTOMER

Join:

inner join

intervalmatch (AccInv,custId)

load

AccumPaidOpen,

AccumPaid,

custId

RESIDENT DataPaid;

----------------------------------------------------------------------------------------------------------------------

Chart table

load three dimensions

(Rename trnNo to Inv Num etc)

custId

trnNo

trnDate

Paid date

TN2

EXPRESSIONS

sum(trnAmount)      //sum invooice

// PAID BY INVOICE

if(AccumPaid >0,SUM(trnAmount),

sum(trnAmount)

+aggr (sum({1}total<custId>TotalPaid),custId,trnNo)

/aggr (count({1} total<custId> TN2),custId,trnNo)

-aggr (SUM({1} total<custId>

trnAmount),custId,trnNo))

//TOTAL INVOICED

aggr (SUM({1} total<custId>

trnAmount),custId,trnNo)

//TOTAL PAID

aggr (sum({1}total<custId>TotalPaid),custId,trnNo)

/

aggr (count({1} total<custId>

TN2),custId,trnNo)

[#4]

hi RJ,

I am checking your approach then will reply

Regards

- Anil

[robert99]

Thanks

Any suggestions or issues will be appreciated

I just used the sample script in the manual but used a value range rather than a date range. So I'm not sure if all the options I have included (like set nullinterpret) are necessary or not

But I'm still learning QV so this was a great exercise for me

It involved

-Accumulation by cust / amount in the script (I had almost given up but solution thanks to the above forum solution)

-Sum group (script)

-Understanding extended interval match.

edit

I can see a problem with drill down by date or invoice (custId is fine). But this could be likely overcome with say aggr and set analysis. But I will work on this next weekend if I have time. (NOW ADJUSTED)

For example

change the total paid (and else section of paid by invoice) to

aggr (sum({1}total<custId>TotalPaid),custId,trnNo)

/

aggr (count({1} total<custId>

TN2),custId,trnNo)

etc

[Anonymous]

Hi Sir 

Can you Please help me to make a automated excel sheet to calculate interest on payment after due date.

i mean invoice must be knocked off on FIFO basis and payment may not be bill to bill.I am uploading a file in which manual calculation of interest is done .can you please help me to get it automated.Customer ledger is also attached .please mail me to

ymoneymantra@gmail.com if you find any solution.

Regards

Abhishek