Dear all,
I am new to Knime so hope you bare with my simple question here below…
I have been hitting my head to get around a simple and basic issue in looping, i.e. to calculate a value that I need to use in the subsequent iteration of the loop…
Put simply, I take a group of rows (transactions) and I read one row at time (chunk loop) I calculate a value which in the next row will be used to set some status in the next row
I tried all and read all posts that I was able to find… no success
Hi elsamuel
first let me thank you for your prompt reply… I am so pleased for that
I tried the suggested loop (reclusive) but did not quite work for my case because:
1- a reclusive loop will read all the rows in input and send them back to beginning as many time as the loop is programmed to execute
2- my case is:
before loop: set amntSold = 0
enter loop:
take record in input and check status and amount
calculate new value for amntSold based on value calculated previously (in previous record)
go back to start of loop
Exit loop when there are no more records to process
My solution sometimes is just to write something into a table and read that table back in the next instance of the loop. Not very elegant but it works.
Thanks a lot for your help… I took a bit of time to put together the part of the workflow in which I am stuck …
The excel file contains data to use with the workflow
Hello
Thanks for your reply;
a table in the filesystem right?
well, In one of my trials, indeed I tried to use a table to hold the intermediary loop values… the values were written into the table (file) as expected but the workflow read the file once at the beginning and did not re-read the updated file !
Sometimes such a setting can be somewhat over-engineered but it would work.
Maybe you could provide us with an example demonstrating your exact setting (without giving away any secret data). Then we could address your specific situation.
The file contains the data to start with and should be read only once
The first (outer) GROUP LOOP takes set of records grouped by “Market” and pass them through to the CHUNK LOOP (inner) which I configured to read one record at time
The records are sorted so I start from last one
As the inner loop executes it adds the amount of the record “Amount” to the buffer “FVamntSold” if only the record has “Type == SELL” ; As the inner loop executes I need to use the value stored in “FVamntSold” from previous records;
When the last record in the inner loop is processed the loop ends and pass control to the outer loop;
The “FVamntSold” will be set to 0 and the outer loop will start with the next group of records
PS I really did not get what you mean by “exact setting” – please note that I am beginner and self-learner; the data in the file is actual data (nothing secret about it); I only extracted a subset of the records;
Thanks for the example I will be checking it out right away…)
Regards,
There is also there LAG node that could give you information from a previous row. If it is just about simple information.
If you have an example you could upload it here for the community to inspect. Edit: I just see you have an example. I will see to check it out.
The previous example is mainly about using the loops and flow variables and intermediate file. The content is not so important. It is important to ‘wire’ the load and write information correctly.
Another idea could be to append the information to a CSV file with a row number and read it back in the loop with the previous iteration. Thereby preserving the information from every loop.
I am a bit lost)
did I describe the issue clear and were you able to upload the the workflow that I uploaded?
I have been trying since this morning to crack down the example you referred with the variable loop and using a file for my case… I did not honestly understand how to deploy it in my case
if I write “FVamntSold” to an external file in the inner loop then how can I force the re-reading of the file at the start of the next iteration in the inner loop?!
“Lag Column” will not address my issue because what I try to do is reading one record at time in the inner loop (not a group of records); the order of the records is important to set the value of “FVamntSold” correctly and that’s why I must read a single record at time
Thanks so much HanaS
Appreciate your help… “Moving Aggregation” does not meet meet the intended requirement in the “Java Snippet”; the logic requires more than a moving average and must process a record at time…
I will keep hitting my head till I crack it… I am new to Knime and I am surprised as such simple feature is so complex, i.e. making calculations in a loop that you want to pick up through the iterations …
Sorry the worklfow does not meet your expectations. For me, your explanation of the problem / the logic is not straight forward (although you put a lot if effort in it). If you are able to add a column with the expected results to the input xls-file, it makes it easier to find and check the solution.
You might have to clearly define what you want to happen. If you just want the Flow variable to be initiated with 0.0 at the start of every inner loop you might just connect a Flow variable to the start of the inner loop.
If you want to try the approach with the storage of information at the end of each inner loop you could try it like this. If you need aggregations per outer loop group there are also nodes for that.
It could very well be you might have to further explain what you want to do and maybe construct a result file that shows your desired result while also containing a representation of your relevant problems (maybe add a few more outer groups) and make an example where it is clear if your Flow variable is always 0.0 by design or by chance.
I do not claim this is your solution, merely a demonstration how this store and read back could look like. I have not checken the formulas in the Java script.
BTW - if you just want to aggregate plus and minus you might just transform the ? sales into negative values with a rule engine and you could then do aggregations with them.
I don’t know how to express my appreciation for the help received from all of you … only words are Thanks, Grazie, Shoukran, спасибо
I got this thing working (a slight change to the workflow in the last post from mlauber71)…
I uploaded the workflow in case some other Knime-ers get stuck into same issue of mine
