Dynamic String for Batch Macro

[Bonediggler]

Experts - 

The issue:

1) I have some sql (someone else wrote) that can only execute for one month at a time 

2) I need to run 24 months of data with this code (i.e. the query needs to be run separately 24 times, one month at a time)

3) The 24 months are dynamic.  For example if I ran the macro this month I would start with June 2017, next month I would start with July 2017 and so on.

4) I am writing a batch macro to process the sql by month and output to Excel and am currently feeding in the months via a text input tool

Is it possible to somehow dynamically generate the list of month values for feeding into the macro?

Thanks in advance!!

[fmvizcaino]

Hi @Bonediggler ,

For that, I think it would be easier to use a dynamic input tool. That way you can dynamically modify your query. See the topic below.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Modifying-SQL-Query-using-the-Dynam...

Let me know if that works for you.

Best,

Fernando Vizcaino

[seven · Accepted solution]

@BonedigglerIt seems like you already have the dynamic replacement part set up using a macro and all you really want is to create a list of 24 months including the current month.

Use a Generate Rows tool to create the current date and trim it to the beginning of the month.

That yields:

Use the current month in a subsequent Generate Rows tool to start with the month 23 months back and add a row by adding a month until we reach the current month.

That yields:

Use a Select tool to reduce the data by removing the current month field, yielding your result:

This is the workflow and I have attached it below as well.