Special column aggregation based on Row_ID

Dear Knime Community,

I need to combine 2 tables into one as follows. Though it was easy, but ...well, your experience would be much appreciated.

Table 1
project_ID (unique) project_name
project1 blabla
project2 blibli
project3 blublu

Table 2

Partner_ID participates_in_project country
partner1 project1 DE
partner2 project2 FR
partner3 project2 LV
partner4 project3 UK
partner5 project3 PL

Expected result table

project_ID(unique) project_name 1st_Partner_ID 1st_partner_country 2nd_Partner_ID 2nd_partner_country etc.
project1 blabla partner1 DE NULL NULL NULL
project2 blibli partner2 FR partner3 LV NULL

i'm currently trying an overcomplicated scheme consisting in splitting Table 2 in many tables using only 1 project at time, them joining them cumulatively with table 1. Up to 40 iterations needed though...

Thank you a lot if you can help,

Hi,

you can solve this more easily with a Joiner --> GroupBy --> Cell Splitter (x2) --> Column Filter --> Column Rename --> Column Sorter. See the attached workflow.

Except the Rename / Sort part at the end, it will still work as expected even if you have more than 2 partners for each project.

Cheers,
Marco.

Dear Marco,

you can't believe how grateful i am for you taking time to help me out. I have been trying since your post, and it's working wonderfully. I need now to rename approx 200 columns, but it will be just fine.

thank you again Marco for your kind help,

 

You are welcome! If you are careful on how your columns are named on aggregation (option at the bottom of the configuration window in the GroupBy node) you may be able to make your renaming task simpler and take advantage of the Column Rename (RegEx) node.

Cheers,
Marco.

Yes, thank you Marco for this final piece of advice. This work is over, thank you again, because I learned a good deal.