creating a sequence in KNIME

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:

image

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.

10 Likes

This is impressive @aworker

1 Like

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 !

2 Likes

Mais de rien @aworker , c’est bien mérité voyons.

1 Like

I skip the french but I agree with @bruno29a . Great work @aworker

1 Like

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

6 Likes

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

1 Like

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

1 Like

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:

image

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

3 Likes

now it runs perfectly. I couldnt have done it alone!
many thanks!

2 Likes

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 :wink:

Please feel free to get back in touch if you have any further questions about its implementation.

Best wishes,

Ael

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.