This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
As you all might know, the Alteryx In-Database tools are a great way to process a large amount of data direct on the database layer without streaming it to your computer. It thus can save an incredible amount of loading time.
Customers however often come back to me and ask how to dynamically filter data from a database with some other information like data from a list/file on their computer without losing the performance of the In-DB functionality.
Alteryx usually solves this with the Data Stream In tool, where the users can bring in data from a different source into their In-DB workflow. On the database a temporary layer is created, and all data treated as In-DB. It can then be joined In-DB as a next step.
However, this procedure often requires specific writing permissions on the respective Database which business users hardly can obtain from their company.
In order to still be able to achieve the desired results Alteryx offers the possibility to use custom SQL code for querying the database.
So, in case you do not have the writing permissions you can simply create a simple SQL statement with the help of standard Alteryx tools like Formula, Summarize, etc. and then use this result for querying your DB with the Dynamic Input In-DB tool.
In my example here I have some data in my Database:
And I want to get the results only for these particular Order IDs:
You see that a simple “Inner Join” would quickly give me the required result.
As we cannot use the In-DB join in our scenario due to lacking write permissions from our company we simply need to adapt the SQL query for the entire table in a way which dynamically filters the table for all our target Order_IDs.
As a first step we create a formula achieving this first query part:
Concatenate it in a second step:
And then, third, combine it with the query for the entire table view (just add the “where” query):
Together with the connection name we can then use it together with the database connection name as an Input to the Dynamic Input In-DB tool: