Can there be a section devoted to performing SQL Equivalent functions...

[FogoFortitude]

I am proficient in SQL and new to Alteryx; I see the answer to many of problems quite easily through the SQL language but want to know how to translate much of that to Optimal Alteryx workflows and would if there are alternative approach to the same thing in Alteryx I like to be able choose the path that is most compatible with my Alteryx skill set at the time eg take the path using an Alteryx Tool; a Function, RegEx or other.

Examples: 

- How would perform the equivalent of a GROUP BY and HAVING Clause 

SELECT A, Count(*)
FROM TABLE
GROUP BY A
HAVING COUNT(*) > 1

- How would I perform the Teradata SQL equivalent of QUALIFY, RANK, ROWNUM and PARTITION BY

- How would I assign a PRIMARY INDEX upon creation of a table using the Alteryx output without having to create the table myself using SQL on Teradata itself (this is necessary to mitigate unnecessary storage usage resulting from skew)

[SeanAdams]

Hey @FogoFortitude - I share your pain, having grown up in SQL, there are many things that I naturally do in SQL in my head and then have to back-convert into Alteryx.

As a general matter - it may be worth asking some of the community management team like  @TaraM to set up a new category under Discussions for people to figure out how to do SQL type stuff in Alteryx, and also to discuss where it makes sense to keep it in SQL (e.g. joining 2 very large data sets on a restrictive primary key would work much faster directly in SQL or in an InDB version of the SQL query).

In answer to your other question - how to do a Having:

- Summarize component

- Filter component right after

For the Qualify; Rank; Rownum; Partition By - I can only answer the Rank & row number - they both have the same answer.   sort the data; and then add a Row ID (using the Row ID component).   However the Row ID is not useful if you're doing a rank across groups (like rank within Region or rank of books read by person) - so there most people use a multi-row function.   If you send me a private message, I'd be happy to show you an example.

For the Primary index - not obvious - but what you do is do an output to a new table, and then run the "Alter table create primary key" SQL statement as a post-sQL on the output control.

Have a good Friday

Sean

[LeahK]

@FogoFortitude -- thanks for sharing. This is fantastic feedback! It sounds like you, and other SQL power users, would find something like an "Alteryx for SQL users" series helpful :)

In the meantime, I would encourage you to pose any SQL to Alteryx translation questions to the community within the current discussion forums.  The Data Prep & Blending board is likely to be the best place for the majority of those types of questions. It's also important to know that a large portion of knowledge base content comes from the questions and answers we see within the forums. 

@SeanAdams -- thanks for the additional feedback and for tagging @TaraM into the conversation. We will make sure to take your feedback into consideration. Thank you again!

[TaraM]

@FogoFortitude, @SeanAdams, and @LeahK our Product team just published an Alteryx guide for SQL users. I created a knowledge base article that links to this guide. I hope you will find it helpful. I will be reviewing the label structure to see if it makes sense to add a SQL  label to assist users in finding SQL based content easier. As Leah points out , questions around this topic could span different discussion areas.

If you come across content in the forums that you think would make good knowledge base articles, please nominate the thread from the Options menu. 

Thanks for this valuable feedback!

[SeanAdams]

Hi @TaraM - just gone through this and it really is an excellent resource.    

The SQL geek in me is very glad that you included the idea of the Cross Join - very useful and underused!

[LeahK]

Thanks everyone for sharing your feedback!  In addition to the Alteryx guide for SQL users, we also added a label to our existing Knowledge Base to help categorize relevant content: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/tkb-p/knowledgebase/label-name/SQL