Hi @VAGR_ISK , sorry I had to step out earlier for 2hrs, but I have to say, I was eager to get back to work on this. Finally I’m back, and I put something together using your sample file.
The workflow looks like this:
And the final results looks like this:
I looks the same as the results you provided with your sample - please double check.
You can add some tweaks to get the final result as you like, but the core logic is in this workflow.
You can rename the columns by extracting the column headers from the original file and replace with them in the final results - I tried to do this, but Knime cannot extract header names made of numerical values, it renames them as Column0, Column1, etc…, you can may be rename the columns to something else beforehand.
All in all, the logic that I’ve used is, for each row from File 2, I go check if the List exist in each column of File 1, and if found, then return value “2”, else return value “1” - this is based on the assumption that by default, all values in File 2 are 1. If that is not the case, then this does not really work, or you could split the data into 2 categories, those that defaults to 1, and those that don’t.
Here’s the workflow:
Lookup in different files with CountIf.knwf (93.4 KB)
EDIT: I added a bit of documentations to the workflow:
Here’s the new version of the workflow that contains the documentations:
Lookup in different files with CountIf.knwf (95.7 KB)