So I am stuck on how to get a desired output with the data I have and could use some help please. I have 3 Tables that I am working with for this situation.
The goal is to have all the IDs from table 2 to be populated number Column 2 (Firm ID) in table 1 (Summary). This is to give a list of each ID that is being tested. Next is to populate the columns after Firm ID with data from Pass/Fail.
Pass/Fail holds all the IDs that are missing a specific piece of data, denoted with “N/A”. If there is data present the cell would be “Y” and if it is N/A the cell would be “N”.
Is it possible to pull in this data from the different sheets into Summary using a loop?
is correct that this kind of output is not really optimal for knime.
Hopefully I understood your problem correctly.
You have table ID and Test with the same IDs, and you want to put then into the summary page?
Are the X Text in your Screenshot actually already filled with IDs you want to match?
If you cannot change the output file format for whatever reason, I see the following solutions (depending on your use case)
Easiest:
Write the results to an new Excel sheet and do Vlookups as excel formulas
Solution with native KNIME nodes:
Read in the summary page to knime from row 5 downwards. So you have a “normal” Knime table
Do your matching through the joining node
Rename the columns to column2, column3 etc
Then read the excel a second, this time only up until the start of the id row
Use the concat node row to put the result and excel template together
Finally you can write the result to a new excel or overwrite the previous sheet
Last option:
use POI with the java snippet node to write/read exacly like you want from/to excel. (KNIME to HSSF - Horrible Spreadsheet Format )
However this will take a bit of java code - not really hard but maybe not the solution for everyone https://poi.apache.org/
)