topic Re: Help Needed On : knock off Debit With Credit on Script in QlikView

Help Needed On : knock off Debit With Credit on Script

Sat, 13 Oct 2012 12:22:21 GMT

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

Re: Help Needed On : knock off Debit With Credit on Script

Gysbert_Wassenaar — Sat, 13 Oct 2012 16:59:34 GMT

Can you explain in more detail what you want? How are amount and recdamt calculated?

Re: Help Needed On : knock off Debit With Credit on Script

Sat, 13 Oct 2012 17:40:24 GMT

hi Gysbert sure,

Sorry for 10 mins late.

Each line in result table is derived from trnTable where trnType=Dr, means each Debit entries of each customer.

1. Amount = trnAmount only

2.RecdAmt : Shows Amount knock off / received against a particular invoice on FIFO basis

For INV-1

a. Ist BNK-1 will knock off against INV-1 with full 50000 and RecdOn is trnDate= 01/05/2011

b. IInd BNK-2 will knock off against INV-1 with full 15000 and RecdOn is trnDate=05/05/2011

c. IIIrd BNK-3 will knock off against INV-1 with partial 5000 only and RecdOn is trnDate=08/05/2011

remaining 10000 of BNK-3 will be knock off in next invoice

For INV-2

a. Remaining 10000 of BNK will be knock off and RecdOn is 08/05/2011

For INV-3

No Credit entry found of custID : a2 thats why RecdAmt is 0 and RecdOn if null

Basicaly

RecdAmt = sum of all knock off amount against a particular Debit entry

RecdOn = replacing with trnDate every time when knock off take place

intermediateResultTable:

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

From this intermediateResultTable FinalResult Table will derived

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	-

Hope I explain properly

IndianQvLover

Re: Help Needed On : knock off Debit With Credit on Script

Sat, 13 Oct 2012 19:40:24 GMT

Thanks and will wait for solution from you.

Regards

IndianQvLover

Re: Help Needed On : knock off Debit With Credit on Script

Sun, 14 Oct 2012 07:46:07 GMT

Is I am asking somthing wrong, Please tell me.

Regards

Re: Help Needed On : knock off Debit With Credit on Script

robert99 — Sun, 14 Oct 2012 10:11:36 GMT

No it's clear what you require

And it is an interesting (and it seems to me quite difficult) challenge

Im unsure how to do it though. Hopefully a QV expert can solve this as it would interest me how to do this (if it can be done)

Re: Help Needed On : knock off Debit With Credit on Script

robert99 — Sun, 14 Oct 2012 11:05:39 GMT

This is my part (not complete) attempt

(It will need more work though as only one customer has paid but this might help you)

step 1

Sort excel by CustID then trnNo then rowID

(or use load order by)

Step 2 load script

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';

LOAD rowId,

trnNo,

trnDate,

custId,

trnType,

trnAmount,

trnAmt,

trnDrAmt,

numsum (trnAmt,peek('Amt')) as Amt,

trnCrAmt

FROM

D:\QlikView2\TestSerialRH.xlsx

(ooxml, embedded labels, table is QVFORUM);

step 3 load three dimensions

(Rename trnNo to Inv Num etc)

custId

trnNo

trnDate

step 4 add 2 expression

Amount expression

sum({<trnNo = {'*INV*' } >}trnAmount)

RecdAmount expression

IF(sum (Amt)>0,

sum({<trnNo = {'*INV*' } >}trnAmount),

if(sum({<trnNo = {'*INV*' } >}trnAmount)

>-sum (Amt),

sum (Amt)+sum({<trnNo = {'*INV*' } >}trnAmount),

0))

NB The last payment date I will let you work out

Re: Help Needed On : knock off Debit With Credit on Script

Sun, 14 Oct 2012 12:10:36 GMT

hi IndianQvLover,

Since Last day I am working on your query and trying to provide a batter solution. But as you explain, as I understand. As per my experience I can say that you have adopt the structural programming approach in this with a lot of if then else endif block and loops and a lot of programming gimmicks.

Basically there is no direct way to solve this type of queries in t-Sql / pl-Sql too. To solve this type of queries in t-Sql / pl-Sql I am using cursors. SQL or RDBMS masters suggest minimum use or not to be use of cursor due to performance issues. I think same here in QlikView too there may be a performance issue with a large data set when using structural programming approach. It is not necessary that others are agree with me. But In may opinion there is no other way and I am working on the same.

As Mr. RJ says that "There is some other ways to solve it" I am sure that any one of QlikView master / champ will give you good solution.

hopefully tomorrow I will give you my solution.

Regards

-Anil

Re: Help Needed On : knock off Debit With Credit on Script

Mon, 15 Oct 2012 13:17:06 GMT

hi IndianQvLover,

Please check attached QVW file. Solution to your query is based on structural programming approach. I could not found other better way may others have.

But I am not satisfied with this salutation because it will reduce the performance due to havey use of if then else block. I will try to suggestion on this from Mr.Swuehl, Mr. John Witherspoon, Mr. Miguel Angel, Mr. Rob and others

But at this it is only I have.

Hope it will help you.

Regards

-Anil

================================================ Script

//---------------------------------------- Transactional Data

trnTab:

load * Inline [

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

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

2, INV-1, 05/05/2011, a1, Dr, 70000,-70000,70000, 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, 2000, -2000,2000,0

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

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

];

//---------------------------------------- Debit Data to be knock off

//--- Load all Debit Transaction i.e. Invoice Details in a separate Table

drTab:

load

custId as drCustId,

trnNo as invNo,

trnDate as invDate,

trnAmount as Amount

Resident trnTab where trnType='Dr' order by custId,trnDate;

------------------------------------------ Data Transformation

Logic Of Data Transformation : This solution is based on structural programming gimmicks

1. Load all Debit Transaction i.e. Invoice Details in a separate Table say drTab

2. Declare some variabale / flage and set there values to 0

2. Start a infinity loop and With in this loop

3. Read first row data from Debit Trn. Table and set vDrFlg variable value to 1

4. Extrect all Credit records in a separate table say crTab for just previously read custId and set vCrFlg=0

5. Read First entry from Credit records table

6. Knock off Debit previously read debit record with credit record and insert each knocked off record in Intermediate result table

7. After each insert, check whether debit entry fully knock off or not then again if not then then repeate steps 5,6,7

8. if yes then repeate steps from step no 3 but for next row

Note : With in infinity loop flow is controled by if then else blocks.

For me it not possible to wright each and every steps of logic here any one has to understand it.

let vCounter=1;let vDrFlg=0;let vDrRow=0;let vCrFlg=0;let vCrCustId='';vCrRow=0;let vDrRecords=NoOfRows('drTab')-1;

do while vCounter=1 //--- Infinity loop

if vDrFlg=0 then //--- Protecting from every time read from debit trn. table till entry is fully knock off

vDrFlg=1

vDrAmt=peek('Amount',vDrRow,'drTab')

vDrCustId=peek('drCustId',vDrRow,'drTab')

vDrInvNo=peek('invNo',vDrRow,'drTab')

vDrInvDat=peek('invDate',vDrRow,'drTab')

if vCrCustId<>vDrCustId then; vCrCustId=''; endif //--- seting custId

endif

if vCrCustId<>vDrCustId then //--- Debit custId changed then drop credit trn table and load credit trn records in credit trn table

vCrFlg=0

vCrCustId=vDrCustId

vBalAmt=vDrAmt

if NoOfRows('crTab')>0 then; DROP Table crTab; endif;

crTab:

load trnDate as docDate, trnAmount as crAmt Resident trnTab where trnType='Cr' and custId = '$(vDrCustId)' order by custId,trnDate;

let vCrRecords=NoOfRows('crTab') -1;

endif

if vCrFlg=0 then //--- Protecting from every time read from credit trn. table

vCrAmt=peek('crAmt',vCrRow,'crTab')

vCrDate=peek('docDate',vCrRow,'crTab')

vCrFlg=1

else

vCrAmt=fabs(vBalAmt)

vBalAmt=vDrAmt

endif

vBalAmt=vBalAmt-vCrAmt

vCrAmt=if(vBalAmt>0,vCrAmt,(vCrAmt+vBalAmt));

intTab: //--- Inserting records in to intermedieate result table

load * inline [

iCustId, iInvNo, iInvDate, iAmount, iCrAmt, iCrDate

$(vDrCustId), $(vDrInvNo), $(vDrInvDat), $(vDrAmt), $(vCrAmt), $(vCrDate)

];

if vBalAmt<0 then //--- Checking whether debit fully knock off or not 0:- fully knocked off

vDrRow=vDrRow+1

vDrFlg=0

else //--- When debit entry partially knock off

if vCrRow>=vCrRecords then //--- Checking Credit trn. table row counter

vDrRow=vDrRow+1

vDrFlg=0

else

vCrRow=vCrRow+1

vCrFlg=0

endif

if vDrRow > vDrRecords then; vCounter=2; endif //--- Checking Infinity loop row counter

loop

//---------------------------------------------------- Final Result Table

finalResultTab:

load

iCustId as rCustId,

iInvNo as rInvNo,

iInvDate as rInvDate,

iAmount as rAmount,

sum(iCrAmt) as rRecdAmt,

max(iCrDate) as rRecdOn

Resident intTab group by iCustId, iInvNo, iInvDate, iAmount;

Re: Help Needed On : knock off Debit With Credit on Script

Mon, 15 Oct 2012 14:48:40 GMT

hi Anil

I check it and found its working fine, it is the same as I need. I will try it with large data set and will tell you about your doubt. But at this time this solution is Grrrrrrrrrrrr88888888888.

Thanks Buddy, Superb and looks perfect

IndianQvLover

Re: Help Needed On : knock off Debit With Credit on Script

robert99 — Sun, 21 Oct 2012 03:36:07 GMT

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

Re: Help Needed On : knock off Debit With Credit on Script

robert99 — Sun, 21 Oct 2012 03:46:19 GMT

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)

Re: Help Needed On : knock off Debit With Credit on Script

Sun, 21 Oct 2012 08:43:21 GMT

hi RJ,

I am checking your approach then will reply

Regards

- Anil

Re: Help Needed On : knock off Debit With Credit on Script

robert99 — Sun, 21 Oct 2012 10:58:43 GMT

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

Re: Help Needed On : knock off Debit With Credit on Script

Anonymous — Mon, 31 Dec 2018 09:38:17 GMT

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