OK, I’m still having conceptual problems understanding how KNIME processes rows of a table individually, rather than (what appears to be the default) treating all rows as a “set”.
For the (draft/faulty) workflow below, I want to:
- Create a table with a single row (“Table Creator”).
- Shift the date into a new column (“Date&Time Shift”).
- Create a new row (“Add Empty Rows”).
- Initialize the date which gets shifted in step 2 (“Rule Engine”).
When the workflow runs, the “Date&Time Shift” node only updates the first row. [Note: I initialize the table (“Table Creator”) with a different date than I actually want the loop to use (“Date&Time Input”), so that I can see what the loop is doing.]
I know the logic is still WAY off, but hopefully you can follow what I’m trying to do with the 4 steps above: build a table row-by-row. Once I figure out what I’m misunderstanding, I can add an “If Switch” node, so that I don’t add a row on the last iteration. I didn’t like the warnings I got when initializing the workflow with an empty table, so I insert a row at the end of the loop, rather than at the beginning which would be my preference.
I’m trying to switch from Access VBA and SQL Server T-SQL to KNIME, but I need to fully understand:
- Processing data row-by-row (this thread).
- Processing data as a set (the KNIME default).
loop_over_rows_.knwf (23.5 KB)
The workflow you posted does not have loop. At the same time it has extra logic that could be done just by Group by without all preliminary steps. To go ahead you really need to catch set logic used by SQL, Pivot-tables so on. Also, I recommend to stick wit SQL Server with KNIME.
Doesn’t having the “Counting Loop Start” and “Loop End” nodes create a loop?
I would like to help but I am not sure what you are trying to do here. The “Counting Loop Start” begins with the same table every iteration, while the “Loop End” collects the result of every iteration by accumulation the resulting table top-to-bottom.
Hi @bassman -
It looks like the workflow file you posted and the screenshot you posted don’t match. Can you post the more recent workflow?
Absolutely: latest version is here. I have both the Beginner’s Luck and Advanced Luck book, and have looked at plenty of samples, but I still don’t “get it”.
At this point, I’m not convinced I can do what I’m trying to do in KNIME:
- Have a loop.
- Inside the loop, create a new row, each iteration.
- Modify the current row, for the current iteration, using various KNIME nodes.
Note: In this workflow, my intent is that:
- The “date” column should always be set to 2018-12-14 (“Rule Engine” node)
- The “Date&Time Shift” node modifies “date” column, adding “current_increment” months.
Generate Table Row-by-row.knwf (23.2 KB)
The main problem with the workflow is that you were passing a date formatted as a string to a node that needs a date input (the Date&Time Shift node). As a result, that node wasn’t actually doing anything. I added a String to Date&Time node to help things along, and cleaned up some extra nodes that weren’t needed.
Generate Table Row-by-row_updated.knwf (21.7 KB)
Is this closer to what you’re looking for?
OMG! OMG! OMG!
Y E S !
I tried it and it works perfectly. The one thing I see, is that when I right-click and select “Step Loop Execution” a few times on the “Loop End” node, I can’t find a node where I can right-click and view results (other than the current row), unless I let the loop run to completion. As I build on this GREAT example (and try to figure things out for myself), the inability to use the debugging/stepping to see the whole table as it is built (not just the current row), will be limiting.
Hopefully I’m just missing something, but based on a previous workflow I did with looping (similar to this topic), I couldn’t see the results until A) the loop completed and B) a node (e.g. “Rule Based Row Filter”) was connected after the “Loop End” node.
Anyway, thank-you, thank-you, thank-you!!!
BTW, which node creates the new row, each iteration? Maybe a function of how loops work, or maybe an option on one of the nodes I missed? I see you removed the “Add Empty Rows” node I originally had (in my faulty workflow)…
The Loop End node performs the concatenation operation at the end of each loop. So in this case, it’s effectively adding the new row each time.
I’ve used KNIME for “table at a time” operations for about 6 months, loading from a couple of web-based API data-sources into SQL Server, but I’m new to the nuances of loops. I’ve only started trying to really learn KNIME in the last couple of weeks.