Alteryx Analytics

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, FROM, WHERE, GROUP BY, HAVING, ORDER BY


SQL Alteryx
SQL Select Query Input Data ToolTo 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.

Input data tool configuration

Select ToolUsing 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.

Select tool configuration
SQL Select Query
Join ToolIn 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.

Join tool configuration

Join ToolIn 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 ToolThe 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.

Left outer join and Union tool

Uniton tool configuration
SQL Select Query Filter ToolLimiting 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.

Fliter tool configuration
SQL Select Query
Summarize ToolIn 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 tool configuration

Filter ToolLimiting 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.

Fliter tool configuration
SQL Select Query Sort ToolThe 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 tool configuration
Alteryx workflow for the SQL SELECT
This is the full Alteryx workflow for the SQL SELECT statement.

CROSS JOIN


SQL Alteryx
SQL statment 2 Append Fields ToolUsing 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 tool configuration
Alteryx workflow appends Distribution Center records to each Customer record.
This Alteryx workflow appends Distribution Center records to each Customer record.

WHERE BETWEEN


SQL Alteryx
SQL statment 3 Filter ToolLimiting 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.

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

WHERE IN


SQL Alteryx
SQL statment 3 Filter ToolLimiting 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.

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

DISTINCT


SQL Alteryx
SQL statment 5 Unique ToolThe 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.

Unique tool configuration
Alteryx Unique Tool Workflow
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.

 

^Top

Try Alteryx Designer for Free Download Now