Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
marwen_garwachi
Contributor II

Calculate the remaining Balance

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 

2 Replies

Re: Calculate the remaining Balance

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
chrismarlow
Valued Contributor

Re: Calculate the remaining Balance

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;