How to create a dynamic filter using a macro

[CesarSanchez10]

Hi - 

So I'm having some difficulty creating a simple macro. All I need is to be able to filter some dates dynamically. There are 73 conditions within my data so I'm trying to avoid creating 73 filters. So I think a macro is the right approach..

I have company data with the column "Trade Date" and want to filter them based on the table below...

Min_Trade Date	Max_Trade Date	Expression
2012-12-31	2013-01-31	[Trade Date] >= '2012-12-31' and [Trade Date] <= '2013-01-31'
2013-01-30	2013-02-28	[Trade Date] >= '2013-01-30' and [Trade Date] <= '2013-02-28'
2013-02-27	2013-03-28	[Trade Date] >= '2013-02-27' and [Trade Date] <= '2013-03-28'
2013-03-27	2013-04-30	[Trade Date] >= '2013-03-27' and [Trade Date] <= '2013-04-30'

Currently I do it manually and I'm using the expression column to filter the data but ideally, I would like to use the Min_Trade Date and Max_Trade Date values to create a batch macro 

which does this for all 73 months dynamically.

Is there a smart way of doing this?

Thank you very much for the help!

[patrick_digan]

@CesarSanchez10  See if the attached can help get you there. I'm making use of the dynamic replace tool. It's no longer in the current help doc, but the old help docs did a great job of explaining how the tool works: https://help.alteryx.com/9.5/index.htm#DynamicReplace.htm

[mmenth]

Here's a batch macro solution!

Best,

mmenth

[jarrod]

if you want to build this using a Batch macro, you can setup a simple 3 tool macro (macro input, Filter, Macro output). then you add a control parameter (the expression field you created) and the action connects the Control parameter to the Filter with the action replacing the filter's formula. The macro will run all input data for each expression (so 73 times it looks like) and spit out the records that match the criteria. There might be a better way to handle that outside the macro, but it's all a matter of your target goal (speed, simplicity, elegance, etc). 

See my attached workflow and batch macro.

[CesarSanchez10]

Thank you very much for the detailed answer. Unfortunately I can't seem to open the workflow since my company is using an older version of Alteryx  11.5. It's giving me an error when I try to open it.

Do you mind posting a screenshot? 

Thanks again!

[CesarSanchez10]

Hello! Thank you very much!

I can't seem to open the package since I'm using an older version of Alteryx.

[jarrod]

Try unzipping the following file instead. I relabeled as 11.5 so both should open although you might need to redirect the file path for the macro

[CesarSanchez10]

I'm getting the following error

[mmenth · Accepted solution]

Here's what the macro looks like:

where the top macro input is the data set you want to apply the filter to, and the bottom are your filters. I used these as examples:

The filter is just simply '[RecordID] = 1'. When you drag on a control parameter you do not need to configure it, but the action tool should be configured like this:

Append the two streams, and add your filter: [dates] <= [Max_Trade Date] AND [dates] >= [Min_Trade Date]. Then add a macro input and save it.

Open another workflow and do the following:

The top input is the same as the top input in the macro, the bottom input is the same as the bottom input in the macro as well. Add a record counter after the bottom one. Insert the macro you just created by right clicking -> insert -> Macro. To configure the macro, make the connections as shown in my screenshot above, then go to the questions tab and select 'RecordID'.

If you run this, it should give you an output like this:

Where RecordID matches up to the filter that you were using. In this instance, only filter #1 and filter #3 returned any resutls.

Best,

mmenth

[jarrod]

So that error means a field hasn't been selected for the control parameter. In the macro, look for a drop-down that has red text and select the appropriate field. If you can, post a package of your workflow and I'll see what I can do to fix the settings.