community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.

SQL Commands in Alteryx

Alteryx_KB
Import

Alteryx provides GUI tools that offer similar functionality to many SQL commands. Although minimal SQL scripting may be necessary in order to properly configure tools, the amount required to complete analysis is significantly reduced.
 
In particular, the following comparison operators are frequently used in filter and formula tools:
 

Comparison Operators Description
== equal to
!= is not equal to
< less than
> greater than
>= greater than or equal to
<= less than or equal to
 
Additionally, the operators AND, OR, NOT, and IN are also used in the configuration of tools such as Filter and Formula. It is also good to note that Alteryx is capable of recognizing both single and double quotes, as long as the syntax is kept consistent, and that tool programming is not case-sensitive.
 
SELECT Input tables by default will show all available columns.   Columns passed downstream can easily be altered or added/deleted by using a Select tool, located in the Favorites Toolbox. This includes changing names and formats, such as V_String to Int, as well as changing the order in which they appear within the table.           1281 1.png
WHERE 'Where' conditions can be controlled using the Filter tool, located in the Favorites Toolbox. By selecting a column, the conventional SQL operators listed above can be used to set conditions to filter on. When the condition specified is met, the data passed downstream can be accessed from the T side of the Filter tool. It is nice to note that data that does not meet the condition is also accessible from the F side, if it is desired for analysis.           1281 2.png
GROUP BY The Group By functionality can be accomplished using a Summarize Tool, located in the Favorites Toolbox. Any column may be selected, and grouped by within this tool. The Summarize Tool also offers a multitude of other functionality, such as counting and common numerical and spatial functions. When using this tool, it is important to remember that only the columns selected and configured within the tool will be passed downstream. If other fields are desired for analysis, they can be easily accessed using a Join Tool immediately downstream.           1281 3.png
JOIN The Join Tool will default to produce an inner join. Fields can easily be removed, re-ordered, and renamed within the configuration of the Join Tool to ease analysis.           1281 4.png
CARTESIAN JOIN Cartesian join functionality can be easily accomplished using an Append Fields Tool. Cartesian joins yield a cross product of the two tables           1281 5.png
LEFT JOIN
(Also known as LEFT OUTER JOIN)
In SQL, a left join, also known as a left outer join, will return every record from the table on the left, regardless of whether it joins to a record from the table on the right. Records without a join will show the missing field as Null. To achieve this in Alteryx, the data that is output from the center of the Join Tool can be union with the data that is output from the left side of the Join Tool.           1281 6.png
RIGHT JOIN
(Also known as RIGHT OUTER JOIN)
A right join, also known as a right outer join, will return every record from the table on the right, regardless of whether it joins to a record from the table on the left. To achieve this in Alteryx, the data that is output from the center of the Join Tool can be union with the data that is output from the right side of the Join Tool.           1281 7.png
FULL OUTER JOIN A full outer join will return the conceptual result of a right and left join. All records from both tables will be returned regardless of whether or not a join occurs. In cases where a join does not occur, Null values will be returned.           1281 8.png
ORDER BY
 
Order by functionality can be accomplished using a Sort Tool. Any field can be selected and sorted using this tool, in either ascending or descending order.           1281 9.png
SUM A SQL SUM command will yield a numeric total for the column selected.  This can be replicated in Alteryx with a Summarize Tool, where any column can be summed by choosing the Sum option from the Numeric drop-down menu.           1281 3.png
MIN A SQL MIN command will return the lowest value present in the column selected. This functionality can be duplicated in Alteryx by choosing the Min option from the Summarize Tool.           1281 3.png
MAX A SQL MAX command will return the highest value present in the column selected. This functionality can be duplicated in Alteryx by choosing the Max option from the Summarize Tool.           1281 3.png
AVG A SQL AVG command selects the average value for a certain table column. This can also be replicated using the Summarize Tool, by choosing the Avg option from the Numeric drop-down menu.           1281 3.png
DISTINCT A SQL DISTINCT command can be combined with a SELECT statement to return an output with unique values for a certain database table column. This can easily be accomplished in Alteryx by using a Unique Tool.           1281 10.png
 
Comments

Hi There,

 

cannot find where i can enter SQL code that will remove and then re apply indexing to my tables (on a SQL server). 

 

Looking to remove the indexing before appending the new weeks data and then re indexing the table. ( want to control the indexing using Alteryx as the rest of the process is and the whole chain could be automated). 

 

Regards,