4 min read

Dynamically Change SQL Queries Using Alteryx Analytic Apps

Dynamically Change SQL Queries Using Alteryx Analytic Apps

As analysts, we frequently find ourselves being asked to do basic data pulls from databases filtered down to a specific user’s needs. This may be sales data for specific products, within a date range, for certain customers, or really anything under the sun. This is typically a simple task for analysts to tackle, but it’s rare the end-user has the knowledge to build SQL queries. Because of this it often falls on us, the analyst. Pending work volume, a relatively simple but often urgent task like this can prevent analysts from getting more complicated projects completed on time. The good news is that if your company is using Alteryx, you can set up an analytic app for the end-users to query the databases themselves. In this blog, I’ll show you how to set up an app that dynamically changes an SQL query to match the end user’s inputs.

The first thing we need to do is to write the base query that the end-user will be updating. For the purposes of this blog, I uploaded a subset of Tableau’s Superstore Data to Snowflake. Drop an input tool on the canvas, and input the query that will be needed. The important parts are to only put what you want in the output in the SELECT clause, and comment out every dimension in the WHERE clause that the user will be given the choice to edit. I start my WHERE clause with ‘1=1’ just so I don’t have to strip out the first “AND” to keep the calculations later on much simpler.

choose table or specify query - alteryx tutorial

The remainder of what we will do will use the Interface group of tools. Let’s start with dropping a check box onto the canvas. I named this “Filter by Product Category?” and opted to collapse the group when deselected. This function will ask the user if they want to filter the data they are pulling by the Product Category field.

Check box configuration - alteryx tutorial

Next, we will connect this check box to our query in the input tool. Do this by dragging the magnifying glass at the bottom of the check box, to the lightning bolt on top of the input tool. You’ll notice that an action tool shows up as an intermediary, and this is where the magic really happens!

Product Category- Alteryx Tutorial

We are going to be updating the input data tool with a formula. To do this, select “Update Value with Formula” in the action type dropdown, and click on the File – Value as the value you want to update.

Configure Action- Alteryx Tutorial

If you click on the ellipsis in the bottom right, you can open the formula box and see all your options. We want this formula to uncomment the line of --AND "Product Category" in ('PRODUCTCATEGORYPLACEHOLDER'). Below is the formula that will accomplish this:

edit formula- Alteryx Tutorial

Let’s talk through what this is doing. #1 stands for the value of the check box. If it’s checked, it will return TRUE and if not, FALSE. If it is checked, we are replacing a string in the Destination (input tool). The specific string it’s looking for is the line for filtering by Product Category in the WHERE clause, and replacing it with the same line, without the double dash marks, thus allowing this line to be used in our query. If it’s false, I just have the same line being replaced with itself with no changes, leaving the line commented out. I do this because I haven’t found a way to just tell the formula to do nothing if FALSE.

If you open the analytic app in debug mode and select the check box, our query now looks like this:

Choose table or specify query

You can see that the Product Category line is now uncommented from the query. The last step we need to do is to replace ‘PRODUCTCATEGORYPLACEHOLDER’ with the values that the user selects. Let’s jump into that now.

I have found that the best way to start is the List Box tool. This tool will allow the user to select as many values as they need in the output. My favorite part of this is that we can dynamically query every possible option in the database, and use those to populate the list box, leaving no potential for someone to enter an invalid value and break the query!

The first thing to do is to format the tool. Enter the title for the tool, select “Generate Custom List”, then change the Start and End Text to be a single quote. The default on the tool is a double quote, but in order for the syntax in the “in” statement to be correct, we need the values separated by a single quote.

Generate custom list- Alteryx Tutorial

After that, we’ll choose “External Source” in the List Values dropdown. You’ll notice that it says it must contain name and value fields. We can take care of this right in the SQL query. In properties, choose the connection to the DB that stores the data and write a SELECT DISTINCT statement for the value you want to populate the list box. For this example, I’m using Product Category. To take care of the Name and Value requirement, I simply repeat the select product category with a different alias on each line as shown below.

Choose Table or Specify Query- Alteryx Tutorial

Now, all we have left to do is connect the list box to the input using an action tool. The update we need to make here is very simple. We will just check the Replace a specific string box, and type in 'PRODUCTCATEGORYPLACEHOLDER'. This tells the action tool to take our formatted list of user-selected values, and stick it inside the “in” statement.

Action type- Alteryx Tutorial

The last thing we will do is do a little formatting on the interface designer. I want the product categories to only show if a user selects the check box signifying they want to filter by that dimension. To accomplish this, click on the product categories box in the interface designer, then click the up arrow on the right. This will nest it within the Product Category check box, and since we checked “Collapse Group When Deselected” earlier in the process, it will hide the list of product categories.

Product Categorites- Alteryx Tutorial

And that’s it! We now have a workflow that can dynamically update a SQL query based on a user’s input. You can replicate this strategy across any dimension that a user would want to filter an extract. There are endless uses for the data after the query has been altered by the user using all of Alteryx’s groups of tools. You can create a workflow that will make formatted sales reports, custom emails, statistical models, and just simple excel extracts depending on what is needed by the end-user. And since you’re setting all of this up for anybody to run, you also unlock an efficient way for users to get insightful analytics at the push of a button.

What is a Data Monetization? | Unlock Revenue with Data Portals

What is a Data Monetization? | Unlock Revenue with Data Portals

Everyone says, "data is the new gold," and there are a few ways to actually create revenue generation using insights. One such method to unlock the...

Read More
What is Embedded Analytics? | Deliver Insights Directly to People

What is Embedded Analytics? | Deliver Insights Directly to People

Technology has revolutionized how businesses operate, with data being at the forefront of this transformation. The ability to analyze data and...

Read More
Demystifying Data Analytics for SMBs

Demystifying Data Analytics for SMBs

If there’s one problem that most businesses don’t have, it’s a lack of data. In fact, the amount of data in the world is expected to surpass 50...

Read More