I have a list of transactions and I need to adjust entries based upon future transactions. I have a routine that works by using Iterate Table transformation, but it only works well on hundreds of rows, not the potentially millions that are needed.
The problem seems to stem from the fact that the routine needs to return a value for the current row and also mark the future row as being used. I do this by keeping a table of credits and importing/exporting the table in the subproject. If there was a way to return a modified table from a project, instead of appending the rows, I think that might help. The table sizes are too large to append the entire set for each loop.
In this example, Joe bought 10 apples and then took them 5 back to the store in one transaction, and another 2 in another transaction. That leaves his original transaction with 3 apples.
Jane bought 20 oranges total, in two transactions, and returned 15 in a transaction. That leaves her with 5 oranges on her original transaction.
Is there another way to solve this problem that performs well?
Sample project is attached
returns test.zip (74.4 KB)