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 · Accepted solution]

@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.

[anxs]

@RodL

Thanks for your help. I will need additional help. You were right, I was copying the SQL code into the In-DB connection so I changed it to the input tool and pasted the SQL code into the SQL editor as you suggested.

I created a txt file with values 201401 thru 201412, one per row.

I connected a Dynamic input tool to the right of the Input tool. On the input data source template I selected the txt file. I clicked on Modify SQL Query and selected SQL: Update Where Clause. This is where I got lost.

The replacement Field drop-down menu correctly recognizes whats on the SQL in the input statement as I see them as options so I selected MO_NUM. Everything else I left blank.

I connected a Output tool to the right of the Dynamic Input Tool and did what you said and appended the suffix.

The result of all of this were 12 tables, with the correct suffix but each table is just one record, one column and it contains the values from my text file. What am I doing wrong?

[RodL · Accepted solution]

I think I see where the misunderstanding is. I'll try to walk through what it looks like with an example from my local SQL Server instance.

The main workflow should look like this...

To configure the Dynamic Input tool, you set up the Input Data Source Template to be the connection to your database and you then put your query in SQL Query there...

As you can see, I have my select from the table with a WHERE clause. 

Then to configure the dynamic part, you click on Modify SQL Query, click on Add, then SQL: Update Where clause, and a dialog box should pop up...

Here is should have the WHERE clause parameter (in my case, ZIP = '99999') from the first drop down. The text to replace would be the actual date value you had put in the SQL Query (in the template for the Dynamic Input tool), and then the Replacement Field would be the name of the field that has your list of months in it...in my case it is 'Key'.

Based on what you said, you used the date file you made as the template, but you really want the SQL query as the template. It will run the query once for each line in the file coming into the Dynamic Input tool. Sounds like you have it backwards, with the query in the Input tool and the month list in the 'template' of the DI tool.

Not that you can connect, but I also have attached my sample workflow that I got these screenshots from.

Hope this makes sense. Let me know if not.

[anxs]

Thank you! this worked.

[PopeAndrew]

I know this has been answered over two years ago, hoping there is still activity in older threads. 

My question stems off of the text input that is giving the MO_NUM to the dynamic input. Does alteryx have any built in functionality to update the text input with the most recent 24 months ? I was able to adapt these examples to my workflow, the problem I am facing revolves around the 'hard coded' MONUM dates rather than it being an automated process and getting the most recent 2 years. This would be easy if these were dates but MONUM is an integer rather a date.