Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
One of our clients has a strange request but I still have to answer.
The purpose of the exercise is:
-I must calculate the balance of the bills by month of billing. 70% of payments received are related to invoices through a structured communication. But 30% of the payments are not related to any invoice. So I must try to assign payments to the oldest bills and identify the remaining balance.
Should I answer by No it's not possible or can someone see a solution ?
Thanks for your help
Marwen
What is the logic to identify which is "Invoice"? If you can create, Flag on that we can define?
Hi,
You will need a lot more from your client I think but it is possible to script allocating matched & unmatched items, the following shows a simplistic approach and I suspect is very easy to break.
Cheers,
Chris.
Ps I am on DD/MM/YYYY dates
invoice:
LOAD * INLINE [
invoice_id, date, amount
1, 01/01/2019, 100
2, 01/02/2019,50
3, 01/03/2019, 75
4, 01/04/2019, 100
];
reciept:
NoConcatenate
LOAD * INLINE [
date, amount, invoice_id
01/01/2019, 100, 1
01/03/2019, 75, 3
01/04/2019, 100,
01/04/2019, 25,
];
Left Join (invoice)
Load
invoice_id,
amount as match_amount
Resident reciept;
unmatched_invoice:
NoConcatenate
Load
invoice_id, date, amount
Resident invoice
Where isnull(match_amount)
order by date asc;
unmatched_reciept:
NoConcatenate
Load
date, amount
Resident reciept
Where isnull(invoice_id) or invoice_id='';
allocation:
NoConcatenate
Load
-1 as invoice_id,
0 as allocation
AUTOGENERATE 1;
j=0;
for i=0 to NoOfRows('unmatched_reciept')-1
toallocate=Peek('amount',i,'unmatched_reciept')
do until toallocate=0 or j>NoOfRows('unmatched_invoice')-1
if toallocate>=Peek('amount',j,'unmatched_invoice') then
Concatenate (allocation)
Load
Peek('invoice_id',$(j),'unmatched_invoice') as invoice_id,
Peek('amount',$(j),'unmatched_invoice') as allocation
AUTOGENERATE 1;
toallocate=toallocate-Peek('amount',$(j),'unmatched_invoice');
j=j+1;
else
Concatenate (allocation)
Load
Peek('invoice_id',$(j),'unmatched_invoice') as invoice_id,
$(toallocate) as allocation
AUTOGENERATE 1;
toallocate=0;
end if
loop
next i;
allocation_aggregate:
NoConcatenate
Load
invoice_id,
sum(allocation) AS allocation
Resident allocation
Group By invoice_id;
drop tables reciept, unmatched_invoice, unmatched_reciept, allocation;