This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
For most tools that already have “dynamic” in the name, it would be redundant to call them one of the most dynamic tools in the Designer. That’s not the case for Dynamic Input. With basic configuration, the Dynamic Input Tool allows you to specify a template (this can be a file or database table) and input any number of tables that match that template format (shape/schema) by reading in a list of other sources or modifying SQL queries. This is especially useful for periodic data sets, but the use of the tool goes far beyond its basic configuration. To aid in your data blending, we’ve gone ahead and cataloged a handful of uses that make the Dynamic Input Tool so versatile:
Alteryx provides GUI tools that offer similar functionality to many SQL commands. Although minimal SQL scripting may be necessary in order to properly configure tools, the amount required to complete analysis is significantly reduced.
Alteryx allows you to connect to many different types of data sources. One type of data source you can connect to is a database. Examples of databases are SQL Server, Oracle, Teradata, and MongoDB; amongst many others. There are several connection methods to connect to database sources including ODBC, OleDB, or natively.
One of the powerful things about the Alteryx Designer is that you can do most things related to your workflow right from within the workflow itself. One such operation is creating a Primary Key for your database table and namely using the Pre-Create SQL and Post- Create SQL options in the Input Data and Output Data tools to do that.
What Are Primary Keys?
A Primary Key uniquely identifies a record in a database table. The value of a unique identifier, among other benefits, is that it improves database performance and allows updates on the records.
Primary Keys can be made up of one or multiple columns in the table in a database table. However, a table can only have one Primary Key. Primary Keys cannot be null and must be unique and therefore assigning a Primary Key at least consists of two steps: setting the column to Not Null and then setting it to be the Primary key. The values in the target column you’re setting as Primary Key must only have unique values otherwise the database will throw an error. If no column in the table is suitable to be a Primary key, you can use the Alteryx Record ID tool to create such a column.
This article will deal with the case of using one column as the Primary Key. Once you know how to create a one-column Primary Key, you can find many articles online explaining how to create multi-column Primary Keys in SQL.
Creating Primary Keys in Alteryx
All of the following examples assume that you know how to connect to your database.
1. Primary Key for a new table:
When creating a new table in Alteryx and then saving it on the database, the easiest way is to save the table first and then “alter” the table using Post-Create SQL to set the Primary Key.
The Pre/Post-Create SQL statements are dependent on the database you’re using. In this Article you’ll find examples for SQL Server and Oracle but you can get more examples online.
For SQL Server - Expression 1a:
ALTER TABLE ExampleTest1
ALTER COLUMN PrimaryK int NOT NULL;
ALTER TABLE ExampleTest1
ADD PRIMARY KEY (PrimaryK);
For Oracle (10+) – Expression 1b:
ALTER TABLE "ExampleTest1"
MODIFY "PrimaryK" NUMBER NOT NULL;
ALTER TABLE "ExampleTest1"
ADD CONSTRAINT Example_pk PRIMARY KEY ("PrimaryK");
As you’d notice, expressions 1a and 1b are slightly different.
The words with all capital letters are SQL keywords. These are the commands that the database understands.
“ExampleTest1” is the name of the table; replace it with the name of your table.
“PrimaryK” is the name of field that you want to make as Primary Key. In this example it was generated using the Record ID tool.
In Oracle, Example_pk is the name of the Constraint. You can set it to whatever you like as long as it doesn’t have spaces or special characters and it is unique in the database. For example you can use ExampleTest1_pk.
The int word in the SQL Server statement and the NUMBER word in the Oracle Statement are the types of the column in SQL Server’s and Oracle’s parlance respectively. If you’re using the Record ID tool similar to the example workflow then you can keep one of these types. Otherwise, you will have to change it to the correct type. You can get more details of the database types here: SQL Server, Oracle.
Note: for Oracle if you have set the Output Data tool option Table/FieldName SQL Style to Quoted (default) then you must use the quotations around the table and field names; otherwise remove them.
You can confirm that the table now has a Primary Key by using an Input Data tool and checking the Visual Query Builder. The Primary Key will show a key sign next to the field name.
2. Primary Key on an existing table:
If you already have a table in the database to which you want to assign a Primary Key, there are two cases:
The table already contains a column that can be assigned as the Primary Key:
The column must only contain unique values and no nulls.
You can check the column type for the field you want to set as Primary Key using the Visual Query Builder.
Use the Input Data tool and fill Expression 1a or 1b in the Pre-Create SQL Code In this case the table exists in the database and you only need to assign the primary key so you can use the Pre-Create SQL code. Since the retrieved data is ignored, it’s advisable that you limit the number of rows retrieved to only the first 10 records using these SQL statements:
For SQL Server: SELECT TOP 10 * FROM ExampleTest2;
For Oracle: SELECT * FROM “ExampleTest2” WHERE ROWNUM <= 10
2. The table does not have a column/columns suitable to be a Primary Key:
In this case, you need to bring the data to Alteryx, append a field that can be a Primary Key, write the data out, and then set the Primary Key.
You can use the Record ID to create the new field for the Primary Key.
You will have to drop (delete) the table and re-write it to make this change. To make sure that you read all the data from the table you must use the Block Until Done tool right before the Output Data tool.
Similar to the first example, we will put Expression 1a or 1b in the Post-Create SQL. The difference here is that Output Data tool Output Option is set to Overwrite Table (drop).
These are the most common cases of creating Primary Keys and you can use the same logic to create more complex Primary Keys or indeed move some of the SQL table maintenance right into your workflow using the Pre-Create/Post-Create SQL options.
Side note: if you noticed in my screenshots, I have short names for the database connections (if not, check them out). These are Aliases – a neat way to refer to you database connections. If you’re not using them you should check this article.
Common Errors Related to Primary Keys
Here are the most common errors that might point to issues with primary keys:
Primary Key required for Update option…
Make sure a primary key is declared on the table.
Violation of PRIMARY KEY constraint 'PK_TEST'. Cannot insert duplicate key in object 'dbo.TEST'. The duplicate key value is…
You are trying to insert a key that already exists. Make sure you are not inserting duplicates. Is the Primary Key column alphanumeric but not case sensitive? For example SQL server is not case sensitive and EhzA and ehza are considered duplicate.
Note: if the key appears multiple times on the input file and an update option is chosen, the same record will be updated multiple times.
In regular tools…Cannot insert explicit value for identity column in table 'TEST‘….
The key is set to auto-increment and Alteryx is trying to insert a value in this column. Deselect primary key column before appending to table and let the database create the value.
Write Data In-DB …. An explicit value for the identity column in table 'Test' can only be specified when a column list is used and IDENTITY_INSERT is ON….
The In-DB tools cannot generate the SQL statement needed to update a table that has a key which is set to auto-increment. Either change the way the key is generated in your table or use the regular tools.
For further information, please contact Alteryx Support and one of us will reach out to you.
You can find the workflows used in this article attached to this post. When you open these workflows you will get errors - that's expected because your connection details are different. You'll need to update the connection details and table/column names before using them. These workflows have been created with Alteryx Designer 10.1 (10.1.7.12188).
Fadi, Henriette, Margarita
If you are developing with Alteryx's In-Database tools you may want to share the workflows with users who do not have as much database experience. Below is an example workflow to create a macro that will allow your users to select which fields are brought out of the in database tools without interacting with the in database tools directly.
The premise of the process is that you can stream out a single record of your table, then leverage a List Box tool in select mode to de-select the fields your user does not want to pull. From there, you can use Field Summary and Summarize to create a select statement that will be used to update a Dynamic Input In-DB tool.
To run in your environment, update the Connect In-DB tool to a connection that exists on your machine and start with a select all query.
This directory was generated by the Alteryx install script, and contains an instance of the Oracle Instant Client. Oracle Instant Client is a collection of installed Oracle Database libraries for connecting Alteryx Designer to local or remote Oracle Databases. There’s no need for tnsnames.ora file.
How do I create an index on a field in my database table? An index for a field can be created using the Post Create SQL Statement option (option #10) found in the Configuration window of the Input tool.
I am trying to set up a System alias, but the System option is grayed out or missing. How can I create a System alias?
Standard Connection - System connection is grayed out:
In-DB Connection - System connection is not an option:
Alteryx must be run with elevated privileges in order to be able to create a System alias connection within Alteryx. If you have certain permissions, you can run Alteryx as an administrator and then try and create it. Simply right-click on the Alteryx program (or on the Alteryx icon on your desktop) and choose 'Run as administrator' (see below). Otherwise, you will have to get administrative rights in order to add a System alias.
More information from the Help section:
Thanks for reading!
Database aliases can help users save time and allow easier sharing of data. Let's take a look at how.
The first step to creating a database alias in Designer is to establish the database connection. This can be done with the Input Data tool by selecting any of the database options in the dropdown.
Databases we can connect to with Alteryx, an overview.
Once you’ve established the connection through the connection admin, a database connection string will appear, like the one shown below.
Next, let’s go to the Manage Database Connections window (or Manage In-DB Connections window for In-DB aliases). This will be under Options > Advanced Options > Manage Data Connections. Here we can add new aliases, and edit or delete, any existing ones.
Manage Database Connections overview:
There are two types of aliases:
User: Any user can add a User Alias that only they will be able to access and change.
System: System Aliases can only be added/edited by a local administrator.
Sync All allows you to sync Gallery Data Connections
Add Connections allows you to create a new alias (see steps below)
Additional options can be found when you hover over an existing data connection:
Allows you to edit the password of the Alias.
Allows you to delete an alias
When adding a new alias, Alteryx will assist you in making the connection to both MS Sql Server, and Oracle. For all other database connections, select the 'Other' option.
There will be three things to include for 'Other' database connection types: the connection type (user vs system), an alias name and the connection string. The reason we made the connection first through the Input data tool is so that we can simply copy/paste that string into our alias manager.
When the alias has been created you can access this connection through the Input Data tool dropdown under Saved Data Connections. You’ll notice that the string now says aka:AliasName.
Q: What happens when you have multiple workflows that all reference a connection string and your password changes?
A: If using workflows that reference an alias, the user will only have to update the password in the alias manager. If using a regular connection string, every Input Data tool will have to be updated in every workflow that uses that connection.
Q: Can I share, or export, a workflow that has database connection strings?
A: Yes! If you setup an alias on both user’s machines that are an exact match you will be able to share workflows that use those database connections.
Note: The screenshots and steps taken to create the database connection and alias were built in Designer version 11.8.
Need more information on how to use our visual query builder when choosing tables or queries to input? We’ve put together a quick video in v10.1 for your reference:
Find more information on our visual query builder below:
Have you ever wanted to enrich your marketing data through improved data blending and analytics. Now is your chance by connecting to Oracle Eloqua through Alteryx!
*This current connection is facilitated by a third party ODBC driver from DataDirect Connectors. DataDirect has a 30 day trial whereby the ODBC driver can be downloaded for free. This ODBC driver has not been certified by Alteryx and thus is not officially supported.
Setup an account on data direct: https://www.progress.com/odbc/oracle-eloqua
Follow the instructions on the site to download and install the driver.
The link will take you to the screen in the below screenshot.
Please click on the ‘Windows x64’ link in the ODBC drivers section.
This will prompt the download onto your machine, if you receive a security warning click on the on the driver download arrow on the right hand side and select ‘Keep’. *Please check with your IT/ Security protocol prior to doing this step.
Once downloaded click on the DataDirect Cloud Driver install.
This will open a new window, click next until you reach the below screenshot. In this window check ‘Create Default Data Source’, this will create a user DSN account in your ODBC admin automatically within Alteryx.
Click ‘next’, ‘Install’ and ‘Done’ on the following windows.
You can then open Alteryx and drag in an Input tool.
Click on the input tool and dropdown arrow to the database connection folder. Depending on the bit version of your machine, you can either select ‘Database Connection’ for x64 bit or 32 bit Database Connection for x32 bit.
In the next window select ‘New ODBC Connection’ (Below screenshot).
You should then see the Data Source Name ‘DataDirect Cloud 2.0 (User)’
Click on the ODBC Admin button, which will open the ODBC Data Source Administrator. Highlight the ‘DataDirect Cloud 2.0’ Name and the hit Configure.
In the General Tab enter the following information (You should only have to add the Database Name. This would have been created in the Data Direct account).
You can then press ‘Test connect’ which will prompt the ‘Logon to Cloud’ window. In this window enter the ‘data direct cloud username’ and Password. Hit OK after you have done this. You should see ‘Connection established!’
On the security tab back in the ODBC Cloud Driver Setup, please enter in the Authentication section ‘Data Direct Cloud User Name’ as the username. You can leave the logon domain and Data source authentication blank.
If you then click ‘Apply’ then ‘OK’ on the ODBC Cloud Driver Setup
You should still see the original ODBC Connection with the data source as ‘DataDirect Cloud 2.0 (User)’. In this windows just type in your Password for DataDirect, you can leave the username blank (we included this in the security tab in a previous step).
This should then prompt you to ‘Choose Table or Specify Query’ within the visual Query Builder and now you can use Alteryx to make the most of your Eloqua Database!!!
Sometimes you only need to read in specific fields from a database or input. Within Alteryx it is as easy as using a Formula tool, Dynamic Rename and Dynamic input!!
The first step is to bring in the master file and the list of field names you wish to carry through from the Master file.
Next attach a formula tool to the field names list and create a new field with the expression "Keep-" + [FieldNames].
This will rename the fields with “Keep-” appended to the field name.
The next step is to use a dynamic rename tool to “Take Field Names from Right Input Rows”. In the 'New Field Name from Column" select the field which has just been created above.
Use a dynamic select tool to ‘Select via a formula’ and use the expression below to find the field which has ‘Keep-“ appended to the left hand side. This will then only bring through the specific fields from the Master file which you have listed in the field name input.
Using a dynamic rename you can return the fields to their original names through a substring function. This will remove the "Keep-" from the field name.
You now just have the fields you want from the Master input file!
Client Service Representative
Alteryx has the ability to create and query Pre and Post SQL statements. Using this feature is good if you need to create a temporary table, delete or update the table before running the select. With this, you also have the ability to run stored procedures (via exec stored_procedure;) before and after the actual query as well.
How Do I Replicate the WHERE EXISTS Functionality of SQL within Alteryx?
Example 1: UNION - Add rows from Table #1 to Table #2 if the key value of Table #1 does not exist in Table #2. If desired, the combined data set, could be joined with a third data set, only if the key value in #3 does not exist with only one key column.
Example 2: SELECT/UPDATE records from Table #1 based on the contents of Table #2. The statement below generates the names of customers who had orders during 2016.
select c1.customer_number ,c1.customer_name from customers c1 where 1 = 1 and exists (select * from customer_orders c2 where 1 = 1 and c1.customer_number = c2.customer_number and c2.order_year = 2016 ) ;
The SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
The syntax for the EXISTS condition in SQL is: WHERE EXISTS (subquery).
The subquery is a SELECT statement. If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.
To do this in Alteryx (see attached workflow):
Scenario 1: Take the unmatched records from the source you are appending data from out of a join between the two data sets and union it back to the data set you are appending to.
Scenario 2: Create your "look up list" out of a filter for whatever you set as the condition. In this example, a year that is in the data. Join the filtered data back with the other data source. Deselect the data that comes from the filtered source.
Example attached in the v11.3 workflow Where Exists Question.yxmd.