I’m trying to do repeated (inner+left joins) of a table with itself. With each iteration the table will grow in the number of columns as well as in the number of rows.
So, I’m looking at a start/end loop node pair where I modify the table within the loop and then have the end loop send the modified table back to the start loop node.
My first thought was to use the recursive loop nodes but they cannot deal with the change in dimensions. Other loops that I’ve tried will collect the modified table but not send the changes back. I searched the loop descriptions but none seem to do what I’m looking for.
Which loop do I need to achieve this?
Example, joining a table with itself via a left+inner join on its first and last columns, respectively:
Iteration 0:
A B
B C
B C D
C
D E
E F
Iteration 1:
A B C
A B C D
B C
B C D E
C
D E F
E F
Iteration 2:
A B C
A B C D E F
B C
B C D E F
C
D E F
E F
Hi @kermitthefrog01 , the recursive loop is the only loop that will provide you with a modified table to work with on each iteration. If using a different loop, the only way I can think of to handle this would be to read your table data from a file or database table at the beginning of the iteration, and then write it back to that same file at the end of each iteration, but there will be some performance penalties.
You are right that the recursive loop doesn’t handle changing table specification on the collector port, but it can handle it on recursion ports, so with a little ingenuity, you can probably achieve what you need using the recursive loop anyway.
In this demo workflow, I have an empty table and a table containing column names to add on. One gets added on each iteration. The Loop end is set to collect only the final iteration, so it is “protected” from the changing table specification.
On each iteration, the rows that I want to collect are sent to the additional recursion port that I added to the bottom of the loop end. The data from this recursion comes back in on each iteration is the extra bottom port on the Loop Start and is concatenated with the data that is newly added for the current iteration. The concatenation is the data to be sent both to the recursion port and also to the top collection port.
As the recursive loop end has been configured to only collect data from the top port on the final iteration, the result is my table that has been expanded column-wise on each iteration.
Here is the above demo:
If you are having trouble fitting this to your joiner example, please upload a workflow with what you have so far, and I will see if I can assist further.
It turns out that this was the key to the puzzle. I had misunderstood the functionality of the loop nodes, so I was collecting intermediate iterations when I didn’t need them.
When I set the node to “collect only the final iteration”, I got it to run the way I intended.
I really appreciate you going through such a detailed example. It helped me better understand what was going on in my workflow!
Great. Glad to hear it worked for you. I don’t think your understanding of the loop was totally off. Ordinarily you wouldn’t need to do the extra recursive data port, and would just pass the output directly to the collector port, letting it collect on each iteration, so you only needed this additional workaround so you could “manually” handle changing specification.
I’m not quite sure why allowing changing spec wasn’t a built in feature of the collection port, but perhaps there was some technical limitation with the node, or it wasn’t considered important.
