Hi Friends
I will be very grateful if someone can help me with this issue.
“This post will be a bit long because I want to detail well what I need and I believe it’s a bit advanced. I also want to mention that perhaps a solution could be Python or another programming language, but I’m not familiar with it.”
My Goal:
I have a table and I want to calculate the values for each Iteration, **but I don’t want to use loops. Or maybe a way to calculate even faster than loop
Why you don’t want to use loops?
Because I have a large table with hundreds of products and I’ve already completed the task using loops, but I need to iterate through each row to store the last values in a variable and to calculate the next value, and my calculations take approximately 0.7 milliseconds for each iteration. For a table with 160 thousand items, it took 20 hours. (I will show what I did in the end)
Here’s the problem step by step
I have this table with a lot of “cProd” (product_id)
1° First I will need to use a Group Loop Start.
But you said you don’t want to use a loop?
Actually, I don’t want to use a loop row by row , but at least the loop by product group is necessary to use.
Result
2° Rules:
(a) The first row is the initial inventory value from the last month. That means quantity 6, unity value 34,69, total value 208,14 (orange)
(b) the rows below are the invoices that will increase or decrease the inventory. (blue) See that if the code is “1409” the qt_inventory is positive in 1 item, if code is “5927” is negative. (Positive is a new item in inventory, negative is like a selling product that is leaving inventory)
3° Sorter
For my case I need to guarantee the sorter order. I won’t explain the reason in detail, but the order is of extreme importance because first, I need to have a product in stock before selling, and therefore the input needs to come before the output."
4° Column expression then Moving aggregator
Well, if I need to compute the inventory, then I need to use “moving aggregator node” right? Yes 
But first, I need to put the values one below the other, because the moving aggregator requires it to be that way.
Notice that the column “inventory_sorter” is always zero for the initial value and one for invoices. So I use this rule to create a column with all these values together (last column)
4.1 Now “moving aggregator”
Perfect - Moving Aggregator worked fine and I didn’t need to use loop for each product to store the first calculation in memory (variable). This concept is very important.
Before I continue to the difficult part, in my actual project, I had to store the value 6 in a variable and then create a loop only for the first item, calculating 6 + 1 = 7 and overwrite the old variable 6 with 7, so that in the next iteration it would be 7 + 1 = 8, overwriting the old variable 7 with 8, and so on, so that in the next iteration it would be 8 - 1 = 7… and so forth. But notice that up to this point, using the moving aggregator, I didn’t need to loop. I don’t know what the rule is behind the moving aggregator’s source code, but it behaved like a column expression and instantly calculated. Note that if I have hundreds of items, the loop would have to iterate one by one. For me, that takes 0.7 milliseconds (which is a long time). (to propagate values Loop End>Configuration>flag Propagate modified loop variables)
5° Starting Difficult part - Calculate de vl_unit_inventory and vl_total_inventory
Rule: When the code is “1409” I need to sum the vl_total_inventor in the first row + 40,11.
Let’s calculate:
Variable 208,14
if “1409” then 208,14 + 40,11 = 248,25
Well, it seems that I can use moving aggregator again ( 
this is a catch and won’t work on second product. I will show)
Let’s use column expressions again to put those values in the same column like item 4.
var calc1 = column("value1_st")
if (column("inventory_sorter") == 0)
{column("vl_total_inventory")}
else calc1
Great- All in the same column
Now moving aggregator
All right, all most there. But the last row I don’t need the value because the last row is an invoice negative and I need to disregard a value. So let’s replace the last row with “null”
Done
Then I need to calculate the vl_unit_inventory and for that I used more four nodes
The result was PERFECT for the first item because the code “1409” was one below other
Let’s review the final calculation.
Row1 = 6 * 34,69 = 208,14
Row2 = 208,17 + 40,11 = total 248,25 / 7 = unit 35,46
Row3 = 248,25 + 40,11 = total 288,36 / 8 = unit 36,05
Row4 = 288,36 - 36,05 = total 252,35 / 7 = unit 36,05
So, the moving average is PERFECT 
And I didn’t need to use any loops.
**Now, the second item Iteration PERFECT (because there is no “1409” code to change the average)
**Now, the third item Iteration ERROR 
(because there is “1409” code to change the average in the middle)
Those are the correct values.
Row5 = 271,98 + 75,97 = total 347,95 / 8 = unit 43,49
Row6= 347,95 + 75,97 = total 432,92 / 10 = unit 42,39
…
Explanation:
This concludes that I almost managed to perform the calculations without needing to use a loop item by item. If it had worked, I would have the result instantly.
I’m taking 24 hours to get the result for all products and companies.
Can we achieve anything?
Is there any formula within the Column Expressions that stores the value in a variable to be used in the next row? Like a “for i” loop?
Will I still continue using the loop?
This is my real project with loop row by row. Its Big.
But I want another simple and fast way
01.results_with Loop_012024.xlsx (15.9 KB)
Ps: this post
Help__workflow.knwf (207.1 KB)
ps: this post took me 3 hours 
Maybe start with example from here which features both solution with Group Loop and without it:
