Don't miss Inspire 2024, taking place May 13 - 16, 2024 at the Venetian, Las Vegas. Register Now.

Product Guides

The Alteryx Guide for SQL Users

 

Alteryx SQL

The traditional approach to data access and preparation can be time-consuming for data analysts. Some frustrated analysts take matters into their own hands and learn to write SQL rather than rely on IT and SQL developers. 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 sources, including unstructured data. Instead of spending your time testing and debugging code, you construct a repeatable workflow that 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, greater transparency, and more consistency.

 

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

 
 

Select

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

In SQL

 

 

In Alteryx

 

 To access data in Alteryx, you drag and drop an Input Data Tool onto the canvas, locate the database, and simply select.
In this example, the Input Data Tool lets you connect to an SQL Server Database.

  Using the Select Tool allows you change the data type, select/deselect fields, or simply rename the fields to whatever you want. In this case, Customer ID is the field name.

 

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

The Join Tool allows you to join two tables with a common field (primary key) and Alteryx automatically returns three sets of records:

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

Here is a Join on Customer ID.


  In addition to the Join Tool, Alteryx also has an In-Database Join Tool. This enables blending and analysis against large sets of data without moving the data out of the database, and provides significant performance improvements over traditional analysis methods.

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

Learn more about Alteryx In-Database tools here.


   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, two sets of records — the left un-joined records and the (inner) joined records — are joined together, producing a left outer join.

 

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

 

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

In this example, the Summarize Tool lets you group by country (e.g., United Kingdom, Germany), and average the total amount per country.

 You can limiting records based on specific criteria using the Filter Tool. Filtering can use anything from simple comparisons all the way to complicated, conditional statements. This example shows only those records that have average totals greater than or equal to 1000.

 

 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, the Average Total Amount is shown in Descending order.

 


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

 
 

Cross Join

Cross Join

In SQL

In Alteryx

 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. In this example, all Distribution Center records are appended to each Customer record.

 

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

 
 

Where Between

Where Between

In SQL

In Alteryx

 You can limit records to those that have field values within a specific range using the Alteryx Filter Tool. In this example the filter is Order Dates between December 1 – 31, 2016, inclusive.

 

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

 
 

Where In

Where In

In SQL

In Alteryx

 To limiting records to those with field values belonging to a set of possible alternatives, use the Alteryx Filter Tool. In this example, the filter is set to customers whose country is either the United States or the United Kingdom.

 

This Alteryx workflow filters for the customers whose country is either the United States or the United Kingdom.

 
 

Distinct

Distinct

In SQL

In Alteryx

 The Unique Tool in Alteryx helps separate data into two streams, duplicate and unique records, based on the fields you choose. The field chosen in this example is country.

 

Distinct

With the Unique tool, this Alteryx workflow separates the data into two streams (duplicate and unique records), based on the fields you choose such as, in this example, country.

 

Recommended Resources

 
E-Book
Data Transformation with Code vs. Data Preparation with Designer Cloud
In this eBook, learn how Alteryx Designer Cloud provides visual and intelligent guidance to automate and accelerate data preparation so you can get to insights faster.
  • Data Prep and Analytics
  • Data Science and Machine Learning
  • Professional
Read Now
 
Customer Story
Growing a data-culture, naturally, at Roquette
A global leader in plant-based ingredients implemented Alteryx to enable strategic decision-making at scale.
  • Data Prep and Analytics
  • Data Science and Machine Learning
  • Process Automation
Learn More
 
Webinar
FORE! How To Improve Your Golf Game with Data and Analytics
Golf has become increasingly data-driven and analytical. We'll walk you through a Alteryx Designer workflow for creating a "Digital Caddie", helping you improve your accuracy both on and off the green.
  • Data Prep and Analytics
  • Professional
  • Designer
Watch Now