A few weeks ago, I wrote a post on how to resolve query timeouts using batch macros. Shortly after publishing, Mark Frisch of CReW Macros reached out to me to say that my post wasn’t wrong (whew!), BUT there is another way to fix query timeouts without resorting to macros. It’s taken me a while to get around to it, but I wanted to share his solution as well. Read on to learn another way to solve query timeout issues in Alteryx.
Before I dive into this post, I just want to say thanks to Mark for reaching out. Mark reads my work, and when he sees opportunities for improvement, we have a chat. He takes the time to explain the details and answer all of my questions, which I appreciate greatly.
To get going, let’s review my use case.
My Use Case
The workflow we are discussing queries financial and production data. As each day passes, the date range queried needs to change, so I use a Formula tool to calculate the correct date range and a Dynamic Input tool to feed those dates into the query. To save time during development, I queried only one budget node (we have LOTS of budget nodes). Upon completion, I opened up the query to all budget nodes and was promptly met with timeout errors.
As indicated earlier, I used a batch macro to solve the query timeout problems. The batch macro queried one budget node at a time, which was small enough to not time out. Then, the macro unioned the data together. You can see my workflow and the macro in the screenshots below.
This is the batch macro shown above in the red square.
The Dynamic Input Tool
Now, I have used the Dynamic Input tool for quite some time but only used it to update where clauses. My most common use case occurs when I need the dates queried to change or update each time the query runs. For example, if I want to always pull 2 years of data, I create a column called end date using DateTimeNow(). Then, I’ll create another calculated column called start date and use DateTimeAdd to calculate the start date.
Then, I use the Update WHERE Clause option in the Dynamic Input tool to swap out whatever dates are in the query with the calculated dates. Lovely!
Another Solution to Query Timeouts with Dynamic Input
Until Mark contacted me, I didn’t realize I could use the Dynamic Input tool for the same outcome as the batch macro. And, just so we are clear, I mean that the Dynamic Input tool can query one budget node at a time and will then union the results together just like the batch macro.
For simplicity, I removed the tools that build a list of budget nodes to query. Instead, I just put them into a text input tool (shown below). The first thing I do is calculate my start and end dates that will still be fed into the Dynamic Input, just like I showed above.
This is the Dynamic Input tool. To make it work, you must enter a query (or Data Source Template) by clicking the Edit button. Then, you must tell Alteryx what to modify in the query and how to modify it using the Add button.
Clicking the Edit button takes you to the Visual Query Builder, but I’ve moved to the SQL Editor to show the where clauses. Now, the values entered into my where clauses (‘2020-01-01’, ‘abc’) are irrelevant because the Dynamic Input tool will replace them. They can be anything.
Clicking the Add button is how you tell Alteryx how to modify the query. I use SQL: Update WHERE Clause to update the dates and Replace a Specific String to modify the budget node. These are the options under Add.
Now, I’ll admit that I don’t understand the difference between Replace a Specific String and Update WHERE Clause. In some cases, I would expect them to produce the same result, but they don’t. When I first started using Dynamic Input, I defaulted to using SQL: Update WHERE Clause but found that it frequently didn’t work. The query wouldn’t return any data when I knew there was data. Replace a Specific String did work. I don’t know why. If one doesn’t work, try the other. With that said, SQL: Update WHERE Clause can get tricky when using AND or OR, and in those cases, I always go to Replace a Specific String.
This is what the Replace a Specific String configuration looks like.
And after it runs, I use a Summarize tool to make sure that the end result includes data from all 4 of the budget nodes I fed into it.
So, as you can see, the Dynamic Input tool can be used to solve query timeout issues in Alteryx without resorting to batch macros. I hope you learned something new totoday. Thanks and have a great weekend.
Pingback: Update SharePoint Drop Down Lists Using Alteryx Tools » The Analytics Corner