Macro Loop that modifies input and output

anxs

I am fairly new to Alteryx. Let say I want bring in sales by region for Sept-2015 with the Connect In-DB tool using the following SQL:

Select
Region,
Sum(Sales) As Sales
From
TABLE1
Where
MO_NUM = 201509
Group By
Region

At the end of the Connect In-DB tool there is an Output Data tool that creates a csv file named Sales_201509.csv.

My problem is this. The query above in my database runs fairly quick. But if I change the date range to 1 year, then it takes way longer than if I ran monthly queries 12 times and that problem gets incrementally worse as I add more years.

I want to know if I can do a macro that loops from 201401 thru 201412 and will change both the Where statement in the SQL and the name of the file in the output?

RodL

@anxs,

Welcome to using Alteryx!

First thing I would note is that the In-DB tools are typically used when a user doesn't necessarily want to construct a SQL query from code, but wants to use the workflow process to build out the query that goes back to the database.

From your post it looks like you are familiar with coding in SQL and are actually pasting that into the In-DB connection?

If that's the case, you can also do that just in an Input tool once you connect to the database. You would do that under the tab "SQL Editor". All of the process for whatever query you use is still completed all within the context of the database server.

And then for your specific request, you can also do that in a Dynamic Input tool. Once you have the query in the Dynamic Input tool, you can connect a list of the months that you want (in a Text Input tool, for example) to the front and then use the "Modify SQL Query" to dynamically modify the 'where' clause in your statement.

Then in the Output tool, you can specify at the bottom of the Configuration of that tool that you want to append the file name with a field in your data...which in this case would by the date field.

Please let me know if this doesn't make sense. It is difficult to create an example for a database connection since they are naturally dependent on the environment.

I do find it interesting that it takes longer to do the query on the entire year. It may be in the configuration of the table (possibly indexing?), but I wouldn't think it would take longer to do that than with individual month queries.