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! Alteryx Dynamic Filter Condition Sample.xlsx Alteryx Dynamic Filter Database Sample.xlsx

How to create dynamic filter in database?

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!

Alteryx Dynamic Filter Condition Sample.xlsx

Alteryx Dynamic Filter Database Sample.xlsx

Workflow

Developer Tools

Designer Cloud

Dynamic Processing

Accepted answers

apathetichell

With the way how I set up my workflow - you don't even need to change anything in the macro - I tagged the filter row number (in the section you showed) and send it back out to the main workflow with that tagging. You can build something like:

formula tool - fullpath:

[Engine.WorkflowFileName]"myexcelrule_"+tostring([Filter_Rule_Match])+".xlsx|sheet1"

in your outer workflow. attach a select tool to dump any dummy fields - attach an output data tool. on the bottom of the output data tool\

configure it to replace entire path - and use fullpath as your new full path.

sorry didn't really address this specific question:

1) the macro will loop through every entity fed through the control parameter (ie your rule list).

2) The above discusses how to create the multiple files.

3) that specific section tags the run. The batch macro has two values here 1) the filter rule you are applying 2) the runid. The runid allows you to tag the rows for specific output files.

Reminder - this will not be how in-db syntax will work. for In-DB you'd need a where clause to be fed in - a dynamic data stream in - a data stream out - and pending your file size - you may not want excel as a sync.

All comments

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

apathetichell

so have the formula tool create some filename which is meaningful to you - via the a set formula like:

"my_test_"+datetimeformat(datetimenow(),"%Y_%m_%d")+".csv"

and have your control paramter -> action tool -> formula tool to replace the word test.

you need to feed in your where clause (or changes to your where clause) and your replacement for test in the same row in your outer workflow.

BlueFalcon42

Hi apathetichell,

I have tried to create a macro that will loop the filter condition through but it is not working. Can you please advise?

Filter config.yxmc

Sample output file.yxmd

Alteryx Dynamic Filter Condition Sample.xlsx

Alteryx Dynamic Filter Database Sample.xlsx

apathetichell

This isn't an In-DB setup - and migrating from this to In-DB would be very painful. If you have an IN-DB need - start on IN-DB and use where clauses. this is wrong for non In-DB as well - but it's closer.

My hunch is you want something like this...

Sample output file.yxzp

BlueFalcon42

Hi, sorry I'm not very proficient in Alteryx yet. Can I check with you what is the rationale of including the formula: Filter_Rule_Match and how can I modify the macro to loop through all the filter condition and create the multiple unique output files based on each unique filter conditions?

Filter_Rule_Match.png

apathetichell

formula tool - fullpath:

[Engine.WorkflowFileName]"myexcelrule_"+tostring([Filter_Rule_Match])+".xlsx|sheet1"

in your outer workflow. attach a select tool to dump any dummy fields - attach an output data tool. on the bottom of the output data tool\

configure it to replace entire path - and use fullpath as your new full path.

sorry didn't really address this specific question:

1) the macro will loop through every entity fed through the control parameter (ie your rule list).

2) The above discusses how to create the multiple files.

3) that specific section tags the run. The batch macro has two values here 1) the filter rule you are applying 2) the runid. The runid allows you to tag the rows for specific output files.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703