Feed a Dynamic Query with a Batch Macro

Yay! I learned to build batch macros this week! I’ve wanted to tackle batch macros for quite some time but I didn’t have any use cases. This week two use cases popped up. In my first use case, I feed a dynamic query with a batch macro. More specifically, I feed a list of wells into the dynamic query. I’ll show you how to do this in blog and video. Read on to learn more.

Video

Here is the video version of how to feed a dynamic query with a batch macro, here it is (less than 5 min). If you prefer to read, scroll down.

Use Case

Here is my “before” workflow. I query a database and ask for the frac record where the end date is null or the date diff between start date and end date is less than 10. After a little bit of data conditioning, I feed that list of wells into a dynamic query. How do I do that? Read on. Wait…I thought we were going to use a batch macro? We are, but I want to show you where I started from and where I wound up.

To get a list into the dynamic query, I use a Formula tool to put quotes around jpp idwell, my well identifier. (Ignore the error)

Next, I use a Summarize tool to concatenate all of the idwells.

After the Summarize tool comes another Formula tool to put parenthesis and quotes around the concatenated string so I can feed it directly into a query.

Finally, in the Dynamic Query tool, I replace the idwell in the template query with my concatenated string of idwells.

This was working fine, but I noticed I was seeing truncation errors after the summarize. I think it was still doing what it was supposed to be doing, but I had a call with the new Alteryx Virtual Conference Center, so I asked them about it. I spoke with Joe, and he suggested a batch macro to populate the dynamic query instead of the summarize, which is a much more scalable solution. And, it was super simple. Thanks Joe! Here’s how we did it.

Batch Macro

Here is the batch macro I built. As you can see, it’s super simple and only contains four tools. There is a Control Parameter tool, Action tool, Input (with my query), and an Output tool.

The Control parameter is simply named WellID. There is nothing else in it.

I named the Action Tool “Updated Value” and have attached it to the query. Now, when I clicked on “File – …”, it populated the Replace a specific string with the ENTIRE query, which is clearly not what I want. I only want to replace the idwell. I did a quick copy and paste into NotePad and re-pasted in only the idwell that I want to replace. Once the macro is in my workflow, it will feed one idwell into the query at a time but all the results will be appended much like a union.

To add the batch macro to my workflow, I simply right-click in the whitespace and select Insert. I navigate to the macro and then connect it to my last tool. Then, I tell the macro to feed in one idwell at a time. And that is how you feed a dynamic input with a batch macro. Are you impressed? I’m impressed. And it was so easy!!!

Content Created With

Alteryx 2019.4.8

Other Alteryx Posts

• Using the In Operator in Alteryx Filter Tools
• How to Create a Rank in Alteryx
• How to Control the Order of Operations in Alteryx