Using a "dictionary" or "key" in a table to label another table.

[ssheriff]

Hello,

I have two Excel sheets:
(1) the “key” that I’ve filtered to two columns: medication_name (the drug, string) and cancer_drug (whether the drug is a cancer drug or not, 0s and 1s).

(2) a large table that for now I’ve filtered to just a single column: medication_name.

I want to use the first table to create a “key” or “dictionary” (unsure if I’m using these terms appropriately) to append a cancer_drug column to the second table, using the values listed in the first table.

Issue:
I tried doing so using the attached workflow; however, when I applied the same types of nodes to the actual data, I only got "1"s and no "0"s.

example reference table:

example data table to append:

example desired output of data table, achieved using example workflow:

example workflow I made that works on the example dataset but not the actual dataset:
test_myeloma.knwf (9.4 KB)

screenshot of workflow for actual dataset:

example of what workflow on actual dataset is returning:

Any hints as to what I could be doing wrong would be much appreciated.

[ArjenEX]

Hi @ssheriff

You are probably better off with a Joiner node here with medication_name as join columns on both sides. Choose between inner join or left outer join depending on what your use case requires.

Select cancer_drug as the right column to be included.