Looping and comparing two files with different format in KNIME

[majecal80]

Hello,
I would like to ask your help about the issue I am facing for a week. =((
I have two files

CSV File1 Content (2 columns, 3 rows)
Author 1,select * from db.11 join db.22
Author 2,select * from db.22 join db.3333
Author 3,select * from db.3333

CSV File2 Content (1 column, 4 rows)
db.00
db.22
db.3333
db.44

I need to join 2 csv files, then compare the SQL (2nd column) from the first file to second file using LIKE condition).
The result should be below, the author (first file) and all the tables (second file) found in the SQL should be displayed (db.11 is not existing in the second file so it should not be displayed)

Output
Author 1, db.22
Author 2,db.22
Author 2,db.3333
Author 3,db.3333

Thank you.

[bruno29a]

Hi @majecal80 and welcome to the Knime Community.

This is probably not the proper topic to create this post. The KNIME Development topic is probably for developing KNIME itself. This should be under the KNIME Analytics Platform instead. Leave it here, someone will eventually move it, but this is for your future threads that you will create.

Regarding your question, you can use the Cross Joiner and then do some filtering.

Doing LIKE comparison may work for the sample records that you have, but it can be an issue if let’s say in your CSV File2, you also have db.222 or db.2222, etc. With a LIKE, db.22 will “qualify” for db.222 and db.2222.

So, you have to really look for db.22 if that’s the value you want to look for. There are a few ways to do this. You could convert each items to terms and then then look for them from the other file, or you could use regular expression to make sure that you get values as individual values, etc.

In my case, I convert the tables in the SQL as a list, and then check if the table names from File 2 exist in the list.

This is how I did it:

I have the same input data as you have:

Results:

Here’s the workflow: Join 2 files with like.knwf (15.6 KB)

[majecal80]

Thank you so much for your response. Really appreciate it.
With regards to the SQL, it’s not only “join” is placed between the tables.

Author 1,select * from db.11 join (select * from db.22 where …) 22_tbl
Author 1,select * from db.11 as tbl_1 join db.22 as tbl_2

Sorry, but do you have any other advice?

With regards to the category, thank you for the correction. Will take note of that

[bruno29a]

Hi @majecal80 , any reason why you left this kind of example out? I always say “help us help you. The more accurate your information is, the more accurate the solution will be”. It’s better show the various kinds of examples to make sure that the solution will cover these cases.

You can use regular expressions in this situation.

Updated version:

Input data:

Results:

Updated workflow: Join 2 files with like.knwf (13.9 KB)

[majecal80]

Sorry, I did not intently leave the last samples I gave, it just that I forgot that format =(
Sorry for inconvenience… and again thanks for the support especially for the novice like me

[bruno29a]

Hi @majecal80 no worries, unintentionally it can happen

But you do see my point about the accuracy of the solution depends on the accuracy of the information, right?

Keep Kniming

[majecal80]

Yes, fully understand =)
Thanks