Product Guides

The Alteryx Guide
For SQL Users

The traditional approach to data access and preparation is often time-consuming for data analysts in the line-of-business. Relying on IT and SQL developers can often can be so frustrating that you’ve taken matters into your own hands and learned how to write SQL yourself. One of the biggest struggles analysts face in writing SQL is just getting it to work! There's no autocorrect in SQL, so an incorrectly placed period or comma won’t be caught automatically — and can make your entire script fail.

Alteryx takes a different approach with a workflow-based environment that allows you to prep, blend and analyze data from multiple data sources, including unstructured data. Instead of spending your time testing and debugging code, you construct a repeatable workflow that visually shows colleagues across the business – other analysts, IT, and business decision makers – exactly how you extracted and transformed the data. The result? Less time spent writing code, transparency, and more consistency.

We've listed some of the most common data-related processes that many analysts code in SQL, and alongside them show how you would do the same process using Alteryx. These examples are meant to help analysts who write SQL code to understand how to translate their SQL knowledge into an Alteryx workflow.

To learn more about how Alteryx can empower SQL coders, download the whitepaper Enabling Self-Service Data Analytics: 5 Ways Alteryx Helps SQL Users. Then, try it for yourself, and download a free Alteryx 14-day trial.

Select

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

SQL
Alteryx

sql screen

 

 

alteryx input tool   To access data in Alteryx, you drag-and-drop an Input Data Tool onto the canvas, locate the database, and simply select.

In this case, we are using the Input Data Tool to connect to a SQL Server database.

 

alteryx input data config

alteryx select tool  Using the Select Tool allows us to change the data type, select/deselect fields, or simply rename the fields to whatever you want. In this case, we type in Customer ID. 

alteryx select screen

sql screen

alteryx join tool  In Alteryx, combining multiple datasets is easy with the Join Tool. 

With the Join Tool, you join two tables with a common field (primary key) and Alteryx automatically returns three sets of records:

  • Inner Join (J): combine the columns from the source table (L) to the columns in the table to join (R), matching records based on the common field(s).
  • Left unjoined (L): are the records of the original table (L) who have not found a match in the table to join (R).
  • Right unjoined: (R): are the records of the table to be joined (R) who have not found a match in the source table (L).


Here, we are Joining on CustomerID. 

alteryx join screen

Lock Join Tool Alteryx  In addition to the Join Tool, there is also an In-Database Join Tool. The Alteryx In-DB tools enable blending and analysis against large sets of data without moving the data out of the database and can provide significant performance improvements over traditional analysis methods. 

The In-DB Join Tool lets you do inner, left outer, right outer and full outer joins.

You can learn more about the Alteryx In-database tools here.

Union Tool Alteryx   The Union Tool allows you to combine multiple sets of records based on the field name or position of each column. You can easily change the order of the column headers in each set of records so they match up.

Here, we are bringing together two sets of records—the left un-joined records and the (inner) joined records and unioning them together, producing a left outer join.

alteryx union screen

alteryx union tool config

sql screen

Filter Tool   Limiting records based on specific criteria is performed using the Filter Tool. Filtering can use anything from simple comparisons all the way to complicated, conditional statements. In this instance, we are choosing only those records that do not contain the country United States. 

filter screen

sql screen

Summarize Tool   In Alteryx, we use the Summarize Tool to collect data across multiple records, apply an aggregate function, and group the results by one or more fields.

In this example, using the Summarize Tool we are grouping by Country (e.g., United Kingdom, Germany), and averaging the Total Amount per country. 

summarize configuration screen

Filter Tool   Limiting records based on specific criteria is performed using the Filter Tool. Filtering can use anything from simple comparisons all the way to complicated, conditional statements. Here, we are choosing only those records that have average totals greater than or equal to 1000.

filter screen two

sql full configuration

Sort Tool   The Sort Tool in Alteryx allows you to order your data just the way you need it. Select the name(s) of the column(s) and choose between ascending or descending. In this example, we are Sorting the Average Total Amount in Descending order.

sort configuration alteryx

full workflow in alteryx

This is the full Alteryx workflow for the SQL SELECT statement.

Cross Join

Cross Join

SQL
Alteryx

sql screen

append field tool   Using the Append Fields Tool, you can append the fields from a source input to every record of a target input. Each record of the target input will be duplicated for every record in the source input. Here, we are Appending all Distribution Center records to each Customer record. 

append field screen
using cross join alteryx

This Alteryx workflow appends Distribution Center records to each Customer record.

Where Between

Where Between

SQL
Alteryx

sql screen

Filter Tool   Limiting records to those that have field values within a specific range is accomplished using the Alteryx Filter Tool. For this example, we simply filter on Order Dates between Dec 1 – 31, 2016, inclusive. 

filter screen
where between in alteryx

This Alteryx workflow filters Customer Order data specifically for orders placed between Dec 1 – 31, 2016.

Where In

Where In

SQL
Alteryx

sql screen

Filter Tool   Limiting records to those with field values belonging to a set of possible alternatives is accomplished using the Alteryx Filter Tool. Here we are choosing those Customers whose Country is either United States or United Kingdom.

sql screen

alteryx wherein

This Alteryx workflow filters for the Customers whose Country is either United States or United Kingdom.

Distinct

Distinct

SQL
Alteryx

sql screen

Union Tool Alteryx   The Unique Tool in Alteryx helps separate data into two streams, duplicate and unique records, based on the fields of the user's choosing. Here, we are separating on Country.

alteryx select country
distinct in alteryx

With the Unique tool, this Alteryx workflow separates the data into two streams (duplicate and unique records), based on the fields the user user chooses. In this example, we are separating out on country.