# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
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 ?

Marwen

Tags (3)
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)
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:
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
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)
invoice_id,
amount as match_amount
Resident reciept;

unmatched_invoice:
NoConcatenate
invoice_id, date, amount
Resident invoice
Where isnull(match_amount)
order by date asc;

unmatched_reciept:
NoConcatenate
date, amount
Resident reciept
Where isnull(invoice_id) or invoice_id='';

allocation:
NoConcatenate
-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)
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)
Peek('invoice_id',\$(j),'unmatched_invoice') as invoice_id,
\$(toallocate) as allocation
AUTOGENERATE 1;

toallocate=0;

end if

loop

next i;

allocation_aggregate:
NoConcatenate