I’m trying to recreate a workflow that was created in Alteryx, which creates a couple of temporary tables with lists of stores and products of interest, connects to a server/database, and joins the tables - only extracting the stores and products of interest.
In a bit more detail - I have an excel file with a list of store numbers and some store attributes (and an excel file with product numbers and product attributes). That store list is fed into a Data Stream In module and creates a temporary table (same thing for the product list). I then have the Connect In-DB module to connect to the server/database, with some SQL code to include information from specific tables, such as calendar date, sales, etc. and then that table is joined with the temporary table of the store list, so only those stores are pulled from the database, not all stores (and same thing for the specific list of products).
I can do it manually in KNIME by listing all the store numbers & products in the SQL code in the KNIME DB Query Reader but it’s much more efficient having this temporary table + join (no formatting/copy/paste, and less chance of error).
Any ideas how I can achieve this in KNIME?
I’ve attached a screenshot of the workflow in Alteryx with as much detail as possible.
KNIME can handle all sorts of database operations. And you could also (depending on the connected database) using temporary tables or just load the excel file into a database.
You you could simply create the SQL statement automatically. Then what matters is how many IDs you have and what database you use (for example some databases limit IN clause to 1000 entries). The old data base nodes had a nice node for this (database looping) but you can find a component that does the same with the new nodes here on knime hub.
This component should handle your case. eg first you get your stores and products with this component, then you use a joiner to join product to store. Main difference being the last join happening locally on your machine and not in the DB. If this needs to happen in DB, then you will need to go with mlauber71 suggestion. Personally I think mine is more easy to understand.
Instead of having one node to create the temporary table you need two DB Table Creator and DB Insert node.
Another option would be to use the DB Writer node that automatically creates a DB table if it doesn’t exists and then writes the data into it. However this node as of now does not support temporary table creation. But I have added a feature request to add this to a future version of the node. The rest is very similar.
You can find the workflow on the KNIME hub. To read your Excel files replace the Excel File Mock nodes with the proper Excel Reader and adapt the node settings.
