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)
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)
Author 1, db.22
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:
Here’s the workflow: Join 2 files with like.knwf (15.6 KB)
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
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 workflow: Join 2 files with like.knwf (13.9 KB)
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
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?
Yes, fully understand =)
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.