Hello,
I would like to create a sequence in KNIME from the following data records, the IDs mentioned are connected to each other and should be displayed in a column in the correct order.
example:
starts : 1300 - 1311
1000 - 150
1500 - 1000
1311 - 1500
The solution should then be output in one row in several columns
Column 1: Column: 2 Column 3: Column 4:
1300 - 1311 1311 - 1500 1500 - 1000 1000 - 150
Hi @torres000 and welcome to the KNIME community !
This is a graph related problem which can be solved either using the KNIME network (relation graph) nodes or a recursive loop:
I’m providing here the two solutions for comparison:
20220303 Pikairos creating a sequence in KNIME.knwf (138.6 KB)
The two workflows are commented step by step on the nodes but please get in touch if it is not clear enough.
Hope it helps.
Best
Ael
Edit: Sorry @torres000, @ScottF, @goodvirus & @elsamuel for I needed to delete previous post and update it because of a bug. Now this new version is updated and fixed. Thanks for your likes.
This is impressive @aworker
Haha ha, Merci beaucoup Bruno ! Ça me fait plaisir un tel commentaire, surtout venant de toi ! J’espère que les congrès de KNIME reprendront vite un jour et qu’on aura l’occasion d’être en présentiel au même temps pour faire connaissance.
Bonne soirée !
Mais de rien @aworker , c’est bien mérité voyons.
Hi @torres000
I’m posting here an optimized version which should be more efficient in the case of the Recursive Loop implementation:
20220304 Pikairos creating a sequence in KNIME.knwf (192.2 KB)
@torres000 any feedback would be much appreciated.
Hope it helps.
Best
Ael
fantastic this really helps me a lot. i have been looking for a solution for a while and the KNIME community helped me directly. Thank you!!
One last question I have: what is the most efficient way to set up the workflow when you have multiple records matching an id
Example:
First dataset
ID 4000 : 1300 - 1311
ID 4000 : 1000 - 150
ID 4000 : 1500 - 1000
ID 4000 : 1311 - 1500
ID 5000 : 100 -200
ID 5000 : 200 -300
ID 5000 : 400 -500
Solution :
ID: Column 1: Column: 2 Column 3: Column 4:
4000 1300 -1311 1311 - 1500 1500 - 1000 1000 - 150
5000 100 -200 200 -300 400 -500
Hi @torres000
Glad it helped you and thanks for validating the solution.
Concerning your question:
The best way would be to wrap one of the two solutions with a -Group Loop Start- and a -Loop End- nodes, to handle the problem by individual IDs. I would recommend to encapsulate the solution into a component before wrapping it into this extra grouping loop.
This new solution should look as follows (here below fake workflow just for explanation, not really implemented):
Hope the explanation is clear enough. Otherwise, please get in touch and I’ll be happy to help further.
Best
Ael
Hi @torres000
Maybe you have had the time to play a bit with the solution I suggested based on a -Group Loop Start- -Loop End-, which should solve the more general problem you described in your last post.
Besides this possible solution, please find below a generalized solution which does not need adding an extra loop:
20220305 Pikairos creating a sequence in KNIME.knwf (230.6 KB)
This alternative solution should also be much more efficient since it implicitly calculates when the loop should finish based on the minimum number of rows allowed in the final tables arriving to the -Recursive Loop End- node. The loop stops iterating when the third input of the -Recursive Loop End- is an empty table. Again, I have commented every node but please get in touch if you need further help or explanation.
Hope it helps.
Best wishes
Ael
now it runs perfectly. I couldnt have done it alone!
many thanks!
Hi @torres000
Thanks for your feedback and for validating this last version.
My pleasure to help and really glad it worked. I fully understand that the idea of recursion is not obvious and as you could see, we needed several iterations to get to this final more efficient version 
Please feel free to get back in touch if you have any further questions about its implementation.
Best wishes,
Ael
