So let’s walk through the configuration of our Dynamic input.
The key to this tool is bringing in data that we are going to use in our “Where” clause. My example has us pulling record IDs from data set 1. On the tool configuration pane, we have two main sections for this demo. The first is the “Input Data Source Template” at the top of the pane.When you select “Edit…” It brings you to a screen that looks awfully familiar. That’s because it’s the same as the traditional “Input Data” tool. (look you are already half way there and doing great!)
Let’s walk through this step and see how we need to set up our query. Once you have your connection string or alias selected, it allows us to create a query. If we already have the tool set up, we can select the ellipsis (This thing -> “…”) on option 3, and it takes us to the query editor.
I am going to keep the structure of my query anonymous, but the important step here is the “Where” clause. Normally, if I wanted to limit my query based on a list of record numbers in an “IN statement”, I would separate them with single quotes and commas (Like this: Where RSODR# IN (‘1′,’2′,’3’)). For this, I am using the word “Replace”. The field that I am limiting my records on is numeric, so if I would normally use this qualifier, no values would return, but since we are replacing it, we will be okay.
Once that is set up, we need to go to the second part of your tool. There are a few options for changing the what the tool is going to do, but we are going to modify our SQL query, which is the second option. Within the tool there are a few other options, but we are just going to select the “Add” button and select “Add: Update WHERE Clause”
There are a few things to note in this section.
- “SQL Clause to Update” – You will need to select the part of your “Where” clause that needs to be dynamic. Sometimes my clause will not auto populate within the drop down menu, but you can type it in there. You don’t have to add the “where” or “and” to this section, you just need field and “IN Statement”.
- “Value Type” – What is the data type of the field you are using as a qualifier in your “where” clause.
- “Text to Replace” – This is where you select what part of your statement you wish to replace. I like to use ‘Replace’ here because, I always know what I am looking for when I set up the tool.
- “Replacement Field” – This is the field that houses the record information that we are inserting into our data set. for this example it’s the record ID.
- “Group Replacement Value for SQL IN Clause” – This is limiting the amount of characters in the query. This will make sense when I walk you through how the tool works.
Once you are all set up, select “OK” and run the report.
You may notice that there is a lot of stuff happening in your “message” pane while the query is running. What the Dynamic Input tool does, is creates little queries around the information that is passed through the tool. In my example above, you can see where the ‘RSODR#’ now has a list of records that it is querying against. The length of each individual query is based on that 5th piece of the “updating the where clause” pane. Alteryx will add record numbers into the “IN statement” until the query reaches that query character size limit. You should play around with the amount of characters that you are allowed in a query, because obviously if a query is allowed to look for more ID numbers, you will have fewer queries. *phew*
This functionality can create a few problems, so the tool is not optimal for all workflows and data sets. If you are pounding your server with a bunch of tiny queries one after another, that can also lead to issues. Test things out and see what works best for you.
If you have any questions about how I set up this tool and how it can help you, please feel free to email me and I would love to set up a call or webex and we can go through some things.