SQL Commands in Alteryx

[Alteryx_KB]

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.
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.
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.
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.
CARTESIAN JOIN	Cartesian join functionality can be easily accomplished using an Append Fields Tool. Cartesian joins yield a cross product of the two tables
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.
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.
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.
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.
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.
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.
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.
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.
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.

[Brett-LeFleming]

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,