Load row by row in a loop while modifying a variable

[alfacentauri]

Hi,
I need to do an elavorated load with a loop to generate data based on variables that need to be updated in each iteration.
i have a table with ids and Amounts
Table A:

Sup_ID	Tota_Amount
id1	$$$
id2	$$$
id3	$$$

that represents a supplier id and a total amount of debt to that supplier.

Then i have a second table with receipts:
Table B:

Date	Sup_ID	Rec_Amount
XX/XX/XXXX	id1	$$
XX/XX/XXXX	id2	$$
XX/XX/XXXX	id2	$$

but this table doesn't know how much of the total debt corresponds to each receipt, so the idea is to make an algoritm that asumming the receipts are ordered by date, from newest to oldest, put flags on the receipts marking them as debts or paids, making the older ones paid if there is no enough debt, or in other words, that older debt is paid.

the algortim itself is something like:

for each supplier:

for each receipt (pre-ordered):

if (Total_Amount >0)

if(Rec_Amount <= Total_Amount)

Total_Amount= Total Amount - Rec_Amount
flag= 'debt'
//Now i know that this receipt is not paid yet, and substract it from the Total_amount

else

Rec_Amount= Total_Amount
Total_Amount=0
flag= 'debt'
//This one is partially paid, that can happen, one receipt can have multiple payments. So the debt is the remaining Total_Amount after all the newer ones are marked as 100% debt.

So. I tryed to make a load script with this idea:

FOR i=1 to NoOfRows('Table_A')
let Id_Supp= Peek('Sup_ID','$(i)','Table_A');
let Debt_Supp= Peek('Total_Amount','$(i)','Table_A');
DO WHILE Debt_Supp> 0
load ... Resident Table_B where Sup_ID = '$(Id_Supp)' Order by Date
loop
next i

And here is the problem, i can't load rows one by one from Table_B and modify Debt_Supp at the same time.

[marcus_sommer · Accepted solution]

I think there are simpler approaches with the use of interrecord-functions like peek() and previous() possible. It may go in the following direction:

t1: load * from B; left join (t1) load * from A;

t2:
load *, 'further logic' as ....;
load *, if(ID = previous(ID), peek('Rest') - Rec, Total - Rec) as Rest
resident t1 order by ID, Date (desc);

You will need some more if-loops to check the Amount <= >= 0 and for the wanted flag as well as for any exceptions which may applied in preceding steps.

[alfacentauri]

Thanks, i found a solution with that help:

first i load the debts in a temporal table, no need to join with the suppliers. Just a little fix to make sure that the data is in order.

then for each supplier i peek at the 2 data i need, the ID, the total Amount of the debt, i save that in variables.

now i create a second table with just the list of debts for the suplier that i am iterating right now, thats just for convenience.

now that i have isolated all the debts (date, amount, id_supplier), i load the final table with:

if (IsNull(Previous('Amount'),

//This is the first row, calculate the logic using the variable with the Total_Amount that i peeked at the beginning of the cycle '$(Total_Amount)',

// If not null then that means that it is not the first row, i do the same equations but with a Peek() to the previos value that i calculated in the table: Peek('New_Total', -1, 'This_Table_Name')
)  AS New_Total

after the load is finished i just drop the temporal table so it can have fresh data on the next iteration, and stays droped after the last one.


the last step is to drop the rows that i added as extra location for temporal values. This probably can be upgraded for better performance on load, but it is working. So, thanks a lot for the help.