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:
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!
Solved! Go to Solution.
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.
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!
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?
Hi, yes! Each row (filter condition) ties into an output file
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.
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?
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.