Join two databases

I would like to join the following two tables (schematic only) to determine the number of hours a caregiver spends with a cancer survivor:

KNIME example.xlsx (8.9 KB)

Patient A in table 1, is the same person as patient A1, A2, and A3 in table 2. I would like to repetitively enter patient’s A data (from table 1) into A1, A2, and A3 (table 2)

Hi @RayHendy66
in Table 2 you only need a additonal column for your patient column where the nummeric values behind the patient and removed. Now you can join the first table to the second using the modified patient column and patient column in table 1 as key.

BR

Hi @morpheus
Thank you for your help, how do transform the patient column to the additional column in table 2 with removed numeric values?

Hi @RayHendy66,
you can use regexReplace in string manipulation node e.g. regexReplace($Name$,"[0-9]","")
or java snippet node with return $Name$.replaceAll("[0-9]","");
BR

Hello @RayHendy66,

welcome to KNIME Community!

To have two tables joined you need a common joining column. That means as, @morpheus said, you should modify patient’s ID from table 2 so it matches patient ID in table 1. If your patient’s ID are really A,B,C… then it is sufficient to take only first character from it using substr() function from String Manipulation node. Additionally to perform joining in KNIME using Joiner (there is also new joiner - Joiner (Labs) which I recommend) you need to have your data in two seperate KNIME tables. (This is just in case you have all data in same Excel as you shared.)

Hope this helps and if you’ll still have troubles I can make you workflow example.

Br,
Ivan

2 Likes

Thank you @ipazin, that worked perfectly

1 Like

Hi @RayHendy66,

glad to hear that :slight_smile:

Br,
Ivan

ipazine wrote:
then it is sufficient to take only first character from it using substr() function

only if he have to manage 26 patients at max :slight_smile:

1 Like

:smiley:

But guess if there is more patients not alphabet letters will be taken as ID

Ivan

1 Like

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