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

[Gysbert_Wassenaar]

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

[#3]

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

[#4]

Thanks and will wait for solution from you.

Regards

IndianQvLover

[#5]

Is I am asking somthing wrong, Please tell me.

Regards

[robert99]

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)

[robert99]

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

[#8]

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

[#9 · Accepted solution]

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

  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;

[#10]

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