Hello,
Apologies if this has been answered before, I exhausted all options before posting this. I’m new to Knime but have a lot of Python and SQL experience… so my struggle is just finding the right nodes and understanding the architecture of passing variables/parameters.
I have a snowflake reader and trying to do a dynamic row filter based on a list of values in an excel file. I saw another post where Table Row to Variable Loop Start was used however i’m still not able to make it work… my questions are:
1- excel row to flow variable: each value has its own variable name, I’m not able to find where to give this a single variable name that i can pass to the DB row filter as one variable list?
2- how does this loop work? pass first variable to row filter then second, third… and finally all results are aggregated automatically?
3- Is it possible to do the same with the Table Column to Variable? is there an additional loop node that I can add?
Note: excel files have values that I want to use to filter the snowflake data, since it might change - I’m trying this variables approach to keep it dynamic.
Hey @denisfi, thanks a lot for your comment… i actually first started with the joiner cos that made more sense me (left, right joining) but knime gave me error cos i was trying to join a db table with an excel table… any thoughts there?
if you’ll make a routine with it, why don’t you put the excel result into a database table first and then, you can use the join inside the database to make it quickly?
I’m trying now to convert the excel table to a DB Table using the create DB table node… and then we’ll attempt joining that with the snowflake db table… hopefully it will work, any other thoughts?
Well, you can make your selection from DB and after the DB Reader to inside the knime path/flow, you can use the join to match, but you won’t work inside the database. After the join, you can set a insert/update info if necessary.
that’s exactly what i’m trying now but unfortunately started getting a new error “Execute failed: Cannot perform CREATE TEMPTABLE. This session does not have a current database. Call ‘USE DATABASE’, or use a qualified name.”
And knime doesn’t allow me to connect the DB table creator to the DB joiner… i made the table as temp in memory only… any suggestions?
you know the problem i have with this solution now is that my table has millions of records… so if i dont do the row filter before i read the data… it will take forever everytime… im back to square zero, i need to find how to load my criteria in a variable list and pass it to the row filter node.
With it, you can create a script to change the info from conditions to match with the flow list or column list… and as a loop node inside the database, can set a limit linke 10k of rows to make this process.
Just to add if you like to use the values in your flow variables in your first screenshots you need to first group them and create a list of those strings so you can reference them in “values” filter variable in Row Filter node
br
i figured that out today… i was having a node missing at the end to make the loop work, my row filter node was taking only first node. Thanks for sharing
