Calculate the remaining Balance

marwen_garwachi — Wed, 14 Aug 2019 13:24:16 GMT

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

Re: Calculate the remaining Balance

Anil_Babu_Samineni — Sat, 17 Aug 2019 14:18:18 GMT

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

Re: Calculate the remaining Balance

chrismarlow — Tue, 20 Aug 2019 20:35:15 GMT

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;

topic Re: Calculate the remaining Balance in QlikView

Calculate the remaining Balance

Re: Calculate the remaining Balance

Re: Calculate the remaining Balance