ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to: Dynamic In-DB filtering without database writing permissions

MatthiasSeil
Alteryx
Alteryx

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.

MatthiasSeil_0-1618215441385.png

 

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:

MatthiasSeil_1-1618215441396.png

 

And I want to get the results only for these particular Order IDs:

MatthiasSeil_2-1618215441398.png

 

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:

MatthiasSeil_3-1618215441400.png

 

Concatenate it in a second step:

MatthiasSeil_4-1618215441401.png

 

And then, third, combine it with the query for the entire table view (just add the “where” query):

MatthiasSeil_5-1618215441402.png

 

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:

MatthiasSeil_6-1618215441408.png

 

 

Done!

 

Result:

MatthiasSeil_7-1618215441410.png

 

MatthiasSeil_8-1618215441413.png

 

 

Please also see attached Workflow for details!

Labels