How to pass filters to In-DB connection before query is executed

[saralisset]

Hi Alteryx family! I am new to Alteryx, but I have been requested to build a worflow with only input data, user inputs, and create excel output - no data transformation at all. Currently my report takes forever to run and this is because I pass the filters after doing select ALL. I read the View in SQL (only one) and then pass filters. 

i want to know if there is a way to capture the filters input by the client before the query is executed in Alteryx, that way the query will not be.SELECT ALL FROM TABLE, but rather SELECT ALL FROM TABLE WHERE…

any idea how to make this work would be very much appreciated.

note* you see 2 inputs. First one is only to get the table column names, so the client can select the attributes they want in the output.

Sara

[PanPP]

Hi @saralisset 

You can leverage a expression directly in the SQL statement filtering out data that you don't need.  https://www.futurelearn.com/info/courses/data-analytics-for-business-manipulating-and-interpreting-y...

You can also leverage In-Db tools (Connect in-db and the filter in-db tool) to accomplish this. When using the In-DB tool, the data won't process across the network so the performance will be better.

Hope this helps.

[saralisset]

Thanks for answering!

The reason I’m not using in-db is bc the values to pass through filters has to be optional and also client will insert it using analytics apps.

How could I collect the values inserted on execution and then pass those values to the DB so the query is run only once?

any other idea?

[TimN]

You can dynamically construct the SQL before the In-DB tools.  Use a Formula Tool.

[saralisset]

Thanks Tim. Trying to do it with analytics apps to make it optional filters. The is when I struggle. 

[simonaubert_bd]

Hello @saralisset 

You can use the Dynamic Input Indb tool to have a dynamic selec query at the beginning, and construct your query with in memory tools such as formula and input from your interface. One of my coworker did that a few weeks agos, works pretty well.

Best regards,

Simon