Relate Payments to Invoices (FIFO method)

JGMDataAnalysis — Tue, 09 Jun 2020 15:16:16 GMT

Hello everyone!

I need to relate payments to invoices following the FIFO (First In First Out) method, that is, the oldest invoices are settled first.

Initially I tried to solve it by following the steps detailed in the answer marked "solution" in the following thread: https://community.qlik.com/t5/QlikView-App-Development/Help-Needed-On-knock-off-Debit-With-Credit-on-Script/td-p/411319

Although the script does what I need, I cannot use it for my case due to the number of records that my transaction table (Invoices and Payments) has, more than 2 million. In 3 hours he had covered only 10% of the invoices.

I would appreciate guidance to address the problem because I am stuck.

I would like to read the suggestions from: @sunny_talwar, @swuehl, @rwunderlich, @hic, @Gysbert_Wassenaar , @marcus_sommer , @tresB , @johnw

Thank you very much to all who like to participate.

I await your comments.

Re: Relate Payments to Invoices (FIFO method)

edwin — Wed, 16 Sep 2020 22:24:24 GMT

i dont think you have any choice but to use some combination of aggregations, peek function, and loop through your data set

there is a possibility that the payment could be for multiple invoices (its not just multiple payments for single invoices - it becomes many to many).

i think you need some sort of customized logic to pair the debits and credits. this may not be achieved by regular table operations using joins. if QV is slow with the looping logic you may want to move this to your ETL prior to QV load

thats my opinion

topic Re: Relate Payments to Invoices (FIFO method) in QlikView

Relate Payments to Invoices (FIFO method)

Re: Relate Payments to Invoices (FIFO method)