Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

The Alteryx Guide for SQL Users

TaraM
Alteryx
Alteryx
Created

sql1.JPG

 

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


 

Check out our handy guide:

The Guide to Alteryx for SQL Users

 

The guide covers SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, CROSSJOIN, WHERE BETWEEN, WHEREIN, DISTINCT and we hope you find it helpful.

 

 

 

 



No ratings
Comments
DanHare
11 - Bolide

Hello ! Is there a new link for this please ?  Thanks !

LukeLukose
5 - Atom

Link is not working

Crydata
6 - Meteoroid

Shame this query from a month ago is unanswered as this is a very useful learning resource.

Link is here: https://www.alteryx.com/resources/alteryx-sql  <<<<<<

 

Same style but Microsoft Excel version is here: Alteryx for Excel | Alteryx

NeilR
Alteryx Community Team
Alteryx Community Team

@DanHare @LukeLukose @Crydata  Thanks for alerting us - the link has been fixed!

KD_dell_ire
6 - Meteoroid

Hi there,

The link is not working for me.

 

While I'm here....I was looking for help around solving an issue I had with "Dynamic Input" tool.

  1. Was trying to use SQL: Update WHERE Clause
  2. was trying to replace text like ('ABC00190205909') using a replacement field which has text  like ('ABC00190205909','DEF00190205909','XYZ00190205909')
  3. However the Dynamic Input failed to run (No Columns Returned) due to it adding additional quote marks and those additional quote marks (''ABC00190205909'',''DEF00190205909'',''XYZ00190205909'')prevented the SQL from executing

Any ideas on this as well as fixing the link?

Crydata
6 - Meteoroid

@NeilR Seems like link is broken again - please can you re-instate?

 

@KD_dell_ire guide wouldn't help for this, it is basic and high-level...

 

In your issue - Alteryx is aware the (input 'replacement') field is text and is trying to help the 99% normal case where the values would not have any quote marks around them (adding quote marks to the text values to allow the SQL to run).

Unfortunately for your case where the text values already have the quote marks(?), adding another set of quote marks makes the SQL fail.

 

To resolve: depends on the SQL table data itself (the source against which you are trying to run your dynamic query) somewhat...in this data does the text have quote marks? I'm going to hope/assume no in which case you just need to remove the quote marks from your input 'replacement' field and you should be good (Alteryx will add a single set of quote marks resulting in one set of quote marks and the query should run).

 

If the quote marks are in your data field itself...more hassle (and bad data practice) - I would approach by adding a field that is the target (WHERE) field but with quote removed and repeat above.

 

Alternative (dependent on use case) for both these situations is to have alteryx construct the whole WHERE clause (ie input 'replacement' field starts WHERE) and dynamic input to replace the whole clause (versus the default I think you will be using initially where just the values within the WHERE clause are being dynamically updated). I think this might eb the 'replace string' option vs the 'update WHERE clause' option. Hope that makes (some) sense

see also:

Modifying SQL Query using the Dynamic Input Tool: - Alteryx Community entrée de données entrée dynam...

NeilR
Alteryx Community Team
Alteryx Community Team

@Crydata doh! So sorry it keeps moving. We've fixed the link - again.