Dynamic SQL Queries in Alteryx

by Frederik Egervari

When connecting to databases within an Alteryx Workflow it can be a useful option to pull the data in dynamically. This way the amount of data that has to be pulled from the database can be reduced to increase the speed of the workflow. Especially when the database stores huge amounts of data this might be required.

The Dynamic Input tool can be used to modify the query.  When connecting to the database you get the options window (Fig.1).

Figure 1: Modify the SQL Query manually by opening the SQL Editor.

By opening the SQL Editor you can see the query Alteryx generates automatically. Here you can manually add the part you want to modify. In this case, the where clause is added to be able to select a certain year.

To modify the query the Dynamic Input tool offers the possibility to change the input dynamically by the input. You can add a modification to the query. Here a replacement of the year in the “where”-clause is made (Fig.2). The replacement can change over time, e.g. always the current year is put into the tool and this changes over time, or it could be depended on other parts of the workflow.

Figure 2: The year in the "where"-clause is replaced with the input year from the incoming data stream.

Now the input data stream changes the SQL query that is sent to pull the data from the database.

Using the “DateTimeYear(DateTimeNow())” Formular to generate the input can for example lead to only pulling the data from the current year which is often a good idea.

Sun 10 Sep 2023

Sat 24 Dec 2022

4 mins read

Wed 31 Aug 2022