How to Create A Dynamic Query in Alteryx

The more I learn about Alteryx, the more I love it as a tool for data wrangling. I recently had 2 use cases pop up where I needed to be able to dynamically change the data being queried. I knew the application could perform this task, but I hadn’t yet learned how to create a dynamic query in Alteryx. Now that I know how I’m writing it up for future reference and other people to use. Two use cases are presented because they are configured differently. Read on to learn how.

Use Case 1 – Feed a Well List Into a Query

It’s common practice in the oil and gas industry to enter into data trade agreements with partners. One of our managers approached me and asked about pulling a chunk of data for a specific list of wells. And, that list of wells would probably change over time. Now, this isn’t a particularly difficult task in basic SQL. It just means writing something like the query shown below. (API identifies the well.)

SELECT Well Name,
API, 
County, 
....
FROM MY_Table
WHERE API IN ('4200025489'.....);

However, writing that statement with about 100 APIs seemed tedious, especially given that the APIs could change. So, I did this in Alteryx instead. The core of the workflow looks like this. I bring in my well list, create a few formulas, summarize the data, and feed it into a dynamic query.

Because I am concatenating a very long string, it gets truncated, which is why I wound up duplicating the core workflow into strings small enough to not be truncated. The final workflow turned out like this.

Now, let me explain the details of it.

Tool 1 (Text Input)

Copy and paste a list of 84 APIs from Excel into a text input tool. These are the wells I want to query. I could also have connected to an Excel sheet.

Tool 2 (Sample, Select Records)

In trying to remember which tools would let me pick specific records, I started off with the sample tool. It pulls the first 15 records. Then, as I duplicated the first stream of the workflow, I realized I needed a different tool, the Select Records tool, to pick specific records.

Tool 3 (Select)

The API column came in as an Int64. I wanted it to be a string, so I changed it in the Select tool.

Tool 4 (Formula)

Because the well list will be fed into a query, I needed to add quotes to each of the API strings.

Tool 5 (Summarize)

Then, I concatenated the API list with a Summarize tool. Compare the result before and after the Summarize tool. Before the tool, I had a column of APIs. After, there is a single record or cell containing all the APIs with quotation marks around each one. It’s starting to look like something that can be fed into a query.

Tool 6 (Select)

I needed the string to be bigger, so I added a Select tool.

Tool 7 (Formula)

Because I will be replacing something like this — In (‘1235685489’) — a Formula tool is needed to add parenthesis to the string of APIs.

Notice the red warning arrow in the top right-hand corner of the result. The error says — This cell has truncated characters. If characters are truncated, my query won’t run correctly or I will lose APIs. This is why I had to replicate the main stream of the workflow and only pull a handful of records at a time.

After replicating with the Select Records tool, the error no longer appears, and I can now feed the string into the Dynamic Input tool.

Tool 8 (Dynamic Input)

In the Input Data Source Template, I have written a very basic query to pull all the columns I want for one API. Then, I configure the tool to Modify SQL Query by Replacing a Specific String. The tool will replace the single API with my list of APIs stored in the Concat_API String column.

The code below is copied from my basic SQL query, and I have underlined what will be replaced by the Dynamic Input tool.

From vinfo Where vinfo.api In ('42127374260000')

Tool 9 (Union)

Finally, I use the Union tool to bring everything together.

Now, I realize this particular workflow setup won’t work for all use cases. If you have 1000 wells, this workflow isn’t an option. But, for 100 wells it was a good solution. If you have a suggestion for how to make it work for 1000 wells, please leave a comment! Now, let’s look at my second use case.

Use Case 2 – Query Data Differently Based on Date

My second use case involves querying finance data. For the most recent 6 months, the data needs to be detailed. The query should include vendor, amount, invoice number, each line item on the invoice, and the cost code for each line item. Older finance data doesn’t need to be as detailed. I still want the amount, vendor, and invoice, but I don’t need each line item on the invoice. And, all the data can be unioned together. Depending on the date, some columns won’t be populated, but that’s ok.

For this use case, I started out with a Date Time Now tool. Then, I calculated the date 6 months prior to today, rounded to the end of the month, and then queried my data sets based upon the date.

Tool 1 (Date Time Now)

I start with a Date Time Now tool to get today’s date. I believe I had to fiddle with the format a bit to get the query to work. If you don’t get results from your query, look into the formatting of your dates.

Tool 2 (Formula)

The Forumula tool calculates the date 6 months ago. Then, I trim that to the end of the month. Now, we have a date to feed into the Dynamic Input.

Tool 3 (Dynamic Input)

The Input Data Source Template contains all the columns I want to query, and I have a “dummy” query date entered. In this use case, I choose to update the WHERE clause rather than replace a string. I had a lot of trouble with this at first because I had my query structured with two where clauses and Alteryx will not let you add two “Update WHERE Clause” conditions. You can only do one per tool.

Tool 4 (Union)

Finally, the union tool brings everything together.

This use case was much more dynamic than the first. It actually changes and updates every day the workflow is run. Now, you should know how to use the Dynamic Input tool to update WHERE clauses and strings. This will help you create a dynamic query in Alteryx.

Other Alteryx Content

I’m a bit new to writing about Alteryx, but I have created a few articles you might find useful.

• Error Reporting with Spotfire & Alteryx
• Schedule an Alteryx Workflow to Run on the Last Business Day of the Month
• Calculate Working Days Between Two Dates