Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Can you explain in more detail what you want? How are amount and recdamt calculated?
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
Thanks and will wait for solution from you.
Regards
IndianQvLover
Is I am asking somthing wrong, Please tell me.
Regards
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)
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
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
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;
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