How to create dynamic filter in database?

[BlueFalcon42]

Hi Community, 

I am working on a workflow in Alteryx where I need to create dynamic filters based on filter conditions stored in 'Alteryx Dynamic Filter Condition Sample'.

Here's a breakdown of what I have:

• File named 'Filter Condition Sample', containing 26 unique filter conditions.
• I want to use these conditions to dynamically filter data in another file named 'Filter Database Sample', ultimately creating 26 separate outputs. 
• I have already formatted the filter conditions in the 'Filter Condition Sample' file to match Alteryx formula syntax, but I am struggling to implement them for dynamic filtering.

I've attached an example of the files for reference. 

Would greatly appreciate any insights or suggestions on how to achieve this dynamic filtering. Thanks in advance for your help! 

[apathetichell]

Your core issue that you want to run a series of distinct SQL queries and retrieve the outputs in different files - correct? The dynamic part is that you are batching your sql? Sometimes the term dynamic would be used because there is a user selection process at runtime (or other selection process) - but you'll always be running all queries - correct?

If so - here is my architecture of choice:

1) outerworkflow - read in your excel file for your where clause

2) inner batch macro.

text input tool with your sql query on one line and a template. column b is your in-db connection name.

second text input tool with boiler plate where clause in column a

union tool - config by position - make sure you set this to have input 1) on top of input 2)...

summarize tool concats column A and then takes the max of column b. dynamic input in-db takes your query and connection datastream out - formula tool - some logic to map your query name to a file/output name -> output data.

batch macro -> action tool -> text input tool 2l - set your action tool to update your where clause.

batch macro 2-> action tool > sheet/filename logic  in formula tool.

3) outerworkflow - place macro on canvas. Connect your where file.

map your query/filename/sheetname to your macro.

4) run.

[BlueFalcon42]

Hi @apathetichell, 

Thanks a lot for sharing this workflow breakdown! It's really helpful.

Could I get an additional clarification on item 2, specifically the second text input and union tool? I am stuck with visualizing this part and perhaps, seeing a screenshot might help.

Additionally, could you also elaborate on the macro formula that can be potentially used?

By clarifying the above points, it will really help to keep me on the right track. Thank you! 

[apathetichell]

it's just two text input tools feeding into a union tool. 

Re: the formula - are you exporting to multiple files/tables/etc? what's the format? and is it fair to assume each query/excel file row ties into an output file?

[BlueFalcon42]

Hi, yes! Each row (filter condition) ties into an output file