Calculate the remaining Balance

[marwen_garwachi]

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 

[Anil_Babu_Samineni]

What is the logic to identify which is "Invoice"? If you can create, Flag on that we can define? 

[chrismarlow]

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;