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.
Let's talk about how to 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, but only if the key value in table#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.
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.
Example 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.
Example 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.
Please see the attached workflow: Where Exists Question.yxmd.
In SQL, you can join on a range, using code similar to the below snippet.
SELECT Column, FruitName, StartDateTime, EndDateTime
INNER JOIN dbo.Greek_Alphabet ON dbo.DateTime BETWEEN dbo.FruitList_StartDateTime AND dbo.FruitList_EndDateTime
Alteryx does not have a "join on range" command, but you can get the same result by appending all records and using a filter tool.
For Advanced Joins: when a value from one file is between (< or >) a value from another file, visit and download the Advanced Join Macro.
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.
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.
Dynamically adding data into your workflows is one of the really great things about Alteryx. Having the ability to be able to adjust what you are bringing into the tool can create tremendous efficiencies. So how do we go about doing this? One example is to use the Dynamic Input Tool. This tool allows you to make adjustments to your SQL queries, Stored Procedures or bring in files with the same schema.
Configuration of the Dynamic Input Tool-
Input Data Source Template - Connect to your data and choose the tables and fields or files you would like to bring into the workflow. This is a “Template”, so you are able to change this template with the configuration of the tool, but this “Template” is necessary to bring in the data.
Note: if your data has different schemas then you want to either fix the schema or create a batch macro that will ignore schema and either load data in by name or position
Read a List of Data Sources - This section is used for bringing in a list of files, changing table names, or adding Suffix and Prefixes to your Table Names. If you are bringing in Excel files, the sheet name will need to be added to the Full Path using a Formula Tool.
Change File/Table Name: Takes the specified database table name and changes it based on the values in the specified field.
Change Entire File Path: Takes the entire specified database input path and changes it based on the values in the specified field.
Append Suffix to File/Table Name: Takes the specified database table name and appends a suffix to it. The suffix is the value in the specified field.
Prepend Prefix to File/Table Name: Takes the specified database table name and prepends a prefix to it. The prefix is the value in the specified field.
Modify SQL Query:
Pass field to the Output: This will allow you to pass fields that you may want to use downstream that will append to your data.
Replace a Specific String: This modification is great if you want to pull different data from your tables or fields. You can also use this to update the sheet name from an Excel spreadsheet if you are querying a specific range of cells. To use this modification you will want an input connection that has a field that you want to replace the specific String with. In the replacement window you will remove any text that you do not want to be replaced, then choose which field will replace the string.
SQL: Update Where Clause: This works similar to the replace string, the difference is that the Where clause will be the only portion that you will be able to update. The Where clause will appear in the Update window. You will be able to specify which portion you would like to update and from what incoming field. Please note that the Where portion of the statement will only show. If you have a statement that has Where (the clause) AND (another clause), the Where (the clause) will only appear in the window.
SQL: Spatial Filter: Does you database have latitude and longitude coordinates for stores, business, or customers? Have you built polygons for trade areas or customer profiling and you want to see who in your database fits into these polygons? This is the filter you will want to use. The filter will determine whether or not your data falls within the bounding rectangle of the polygon. Allowing you to look at the data contained within the polygon only.
SQL: Updated Store Procedure: Stored Procedures in SQL are great, but sometimes you need to make small adjustments to them. This Update will allow you to choose the Parameter to update and use a field from your data to replace a specific part of the Stored Procedure.
Sometimes you may need records removed from a database table before adding in new records. This can be done using the Pre SQL Statement in the Input tool, which will run an SQL query before, for instance, appending new records to the table. However, what if you do not want to run the same delete statement every time? For example, let's say that you need to delete all records that match a particular date for every run?
This article covers how to delete records from a database using an Input Text tool containing a parameter and a macro using the Post SQL Statement. The macro below is an example of how you can do this.
Create a macro that reads in a date from a Text Input tool from a workflow.
Use the first Action tool to update part of the Post SQL Statement with the date being fed into the macro through the Control Parameter from the workflow. We are updating the ‘x’ as shown below, which is being used as a place holder in the Post SQL Statement, with the date being fed into the macro.
In the Post SQL Statement of the Input tool within the macro, we created the Delete statement with a temporary place holder for the date being read in (‘x’ as shown below). The incoming date will replace the ‘x’ as a result of the Action tool in the previous step.
Use the second Action tool to also update the filter in a smiliar fashion as the input tool:
The filter tool is needed because we are using a post-sql statement for the delete query which means that the tool will first read in the data and then run the delete, so the records to be deleted will still be read in. The filter is added to stop the deleted records from being part of the macro output.
Important: The reason this is set up that way is because if the delete statement were part of the pre-SQL query, it would be executed as soon as the workflow has been opened which may lead to unintentional deletion of records. The post-SQL statement won't execute until after the table has been read in.
You may also add a macro output for the deleted records to confirm that the correct records were deleted. This will also give you the opportunity to put records back if you didn't mean to delete them as they can be exported from the Alteryx browse window:
Create a workflow that feeds a date from a Text Input tool into the macro.
When the workflow is run, whatever date you have entered in the Text Input tool in the workflow will replace the ‘x’ in the macro, deleting the record(s) that match that date.
Important: If you change the SQL statement, make sure to make any necessary related changes to the Action tools as well.
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.