Rearranging two columns

Dear KNIME community,

please excuse if this topic is not posted into the right forum section. I am a relatively new user of KNIME and I am currently trying to do the following transformation with my data table consisting of only two columns and many rows.

Each row contains a source and target ID of spots that are all somehow connected. The target ID does appear again in a different row under the source ID column.

I am looking for a way how I can rearrange my table so that all dots that are connected through having the same the same target and source ID are ordered into one column, matching the order of connectivity of each spot.

I have attached one such table as an example.

I would be very thankful for any help or comments regarding this problem.

 

This may not be the best way, but I think you need to:

1.  Use a recursive loop to join target to source repeatedly untill all possible joins are made

2.  Filter out those list which are a subset of a longer list (e.g. 11877 -> 10337 is a subset of 11869 ->11877 ->10337)

3.  Transpose table and ungroup

The attached example I think does what you want.  I've tried to annotate it to explain what each step is doing.  I'm sure with a little more thought about the implementation of 1., 2. would be redundant.

Steve

Thank you ever so much. This is exactly the solution I have been looking for.

As I just recently started working with KNIME and would like to further extend my currently very basic skills, could you suggest any tutorials or recommend any documents that could help me improve my KNIME knowledge?

I would take a look at some of the links here - https://tech.knime.org/knimeFi

Also KNIME TV has some useful video guides - https://www.youtube.com/user/KNIMETV

The forums are also a good place to look to see if anyone else has the problem you have, and if not, then don't be afraid to ask!

Finally, I would recommend just trying things out - take a look at what the nodes available do - most of the time there is more than one way to solve a problem in KNIME (I originally came up with a nested loop method to remove subsets in the example - it worked, but was still running by the time I had implemented and run the alternative!)

Steve

Dear Steve,

I have recently noticed that some points are not included at the end of the workflow and seem to go missing somewhere after the Cross Joiner node. I have been trying to figure out why they are missing but have not yet found any solution.

For example the ID of 11877 is not included in the end result.

Do you have any idea why this could be?

Thanks for your help!

I can see the problem, but not yet the solution - I will explain the problem here just now in case someone is then inspired to see a solution!

After the cross joiner, I filter with a Java Snippet Row Filter so that the 'Left' List is only tested against right lists with one more member.  That eliminates also all those rows where the left list is the longest list, as there are no rows where the right list is 1 longer (otherwise the left list wouldnt be the longer list!)

Does that make any sense?  I will think about solutions, but in case you or anyone else is now inspired!

Steve

In fact, I think the attached does it!

 

Steve