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

Alteryx Knowledge Base

Definitive answers from Designer experts.
 You are using an unsupported browser for translation. Please switch to another browser.

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.
View full article
If you are developing with Alteryx's In-Database tools you may want to share the workflows with users who do not have as much database experience.  Below is an example workflow to create a macro that will allow your users to select which fields are brought out of the in database tools without interacting with the in database tools directly.    The premise of the process is that you can stream out a single record of your table, then leverage a List Box tool in select mode to de-select the fields your user does not want to pull. From there, you can use Field Summary and Summarize to create a select statement that will be used to update a Dynamic Input In-DB tool.    To run in your environment, update the Connect In-DB tool to a connection that exists on your machine and start with a select all query.
View full article
Alteryx has the ability to create and query Pre and Post SQL statements. Using this feature is good if you need to create a temporary table, delete or update the table before running the select. With this, you also have the ability to run stored procedures (via exec stored_procedure;) before and after the actual query as well.
View full article
Question   How Do I Replicate the WHERE EXISTS Functionality of SQL within Alteryx?   Example 1:  UNION - Add rows from Table #1 to Table #2 if the key value of Table #1 does not exist in Table #2.  If desired, the combined data set, could be joined with a third data set, only if the key value in #3 does not exist with only one key column.     Example 2:  SELECT/UPDATE records from Table #1 based on the contents of Table #2.  The statement below generates the names of customers who had orders during 2016.   select c1.customer_number       ,c1.customer_name from customers c1 where     1 = 1       and exists  (select *                    from customer_orders c2                    where      1 = 1                          and c1.customer_number = c2.customer_number                          and c2.order_year = 2016                   ) ; Answer The SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.   The syntax for the EXISTS condition in SQL is:  WHERE EXISTS (subquery).   The  subquery  is a SELECT statement. If the  subquery  returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the  subquery  does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.   To do this in Alteryx (see attached workflow):     Scenario 1:  Take the unmatched records from the source you are appending data from out of a join between the two data sets and union it back to the data set you are appending to.   Scenario 2:  Create your "look up list" out of a filter for whatever you set as the condition.  In this example, a year that is in the data.  Join the filtered data back with the other data source. Deselect the data that comes from the filtered source.   Results:     Example attached in the v11.3 workflow Where Exists Question.yxmd.
View full article
Question   In SQL you can Join on a Range.    SELECT Column, FruitName, StartDateTime, EndDateTime FROM dbo.Fruit_List INNER JOIN dbo.Greek_Alphabet ON dbo.DateTime BETWEEN dbo.FruitList_StartDateTime AND dbo.FruitList_EndDateTime   Can you do the same in Alteryx?   Answer   Yes, you can Join on a Range in both SQL and Alteryx.  Alteryx does not have a Join on Range command, but you can get the same result by appending all records and using a filter tool.  See attached v11.3 workflow and below.       Filter Formula:                                                                                                                                   For Advanced Joins: value from one file is between, > or < a value from another file, visit and download the Advanced Join Macro.
View full article
The guide covers SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, CROSS JOIN, WHERE BETWEEN, WHEREIN, DISTINCT and we hope you find it helpful.
View full article