ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Workflow worked in versions before 2020.3 After upgrading to 2020.3 receive error "An unknown error occurred in II_PushRecord"
View full article
How to download and install SQL Server ODBC drivers.
View full article
Error: "Must declare the scalar variable '@var_name'" when running PL/SQL in Pre or Post SQL Statement Section.
View full article
This KB outlines the workaround for DE18888 where Cyrillic characters from DB2 are not displaying in Designer
View full article
User gets a Win32 Exception when trying to connect to a database or edit a query
View full article
Alteryx doesn't read some unicode characters correctly
View full article
Error: "ORA-00900: invalid SQL Statement" occurs when running a procedure in the Pre or Post SQL Statement for Input or Output Data tools.
View full article
Error while connecting to MySQL, from the Input Data Tool - Error: Input Data (13): Error SQLDriverConnect: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application.
View full article
Snowflake External Tables are not showing in the Alteryx Visual Query Builder tab.
View full article
   Dynamically adding data into your workflows is one of the really great things about Alteryx. Having the ability to be able to adjust what you are bringing into the tool can create tremendous efficiencies. So how do we go about doing this? One example is to use the Dynamic Input Tool. This tool allows you to make adjustments to your SQL queries, Stored Procedures or bring in files with the same schema.   Configuration of the Dynamic Input Tool-    Input Data Source Template - Connect to your data and choose the tables and fields or files you would like to bring into the workflow. This is a “Template”, so you are able to change this template with the configuration of the tool, but this “Template” is necessary to bring in the data. Note: if your data has different schemas then you want to either fix the schema or create a batch macro that will ignore schema and either load data in by name or position . Read a List of Data Sources - This section is used for bringing in a list of files, changing table names, or adding Suffix and Prefixes to your Table Names. If you are bringing in Excel files, the sheet name will need to be added to the Full Path using a Formula Tool.   Change File/Table Name: Takes the specified database table name and changes it based on the values in the specified field. Change Entire File Path: Takes the entire specified database input path and changes it based on the values in the specified field. Append Suffix to File/Table Name: Takes the specified database table name and appends a suffix to it. The suffix is the value in the specified field. Prepend Prefix to File/Table Name: Takes the specified database table name and prepends a prefix to it. The prefix is the value in the specified field.   Modify SQL Query:   Pass field to the Output: This will allow you to pass fields that you may want to use downstream that will append to your data.   Replace a Specific String: This modification is great if you want to pull different data from your tables or fields. You can also use this to update the sheet name from an Excel spreadsheet if you are querying a specific range of cells. To use this modification you will want an input connection that has a field that you want to replace the specific String with. In the replacement window you will remove any text that you do not want to be replaced, then choose which field will replace the string.     SQL: Update Where Clause: This works similar to the replace string, the difference is that the Where clause will be the only portion that you will be able to update. The Where clause will appear in the Update window. You will be able to specify which portion you would like to update and from what incoming field. Please note that the Where portion of the statement will only show. If you have a statement that has Where (the clause) AND (another clause), the Where (the clause) will only appear in the window.     SQL: Spatial Filter: Does you database have latitude and longitude coordinates for stores, business, or customers? Have you built polygons for trade areas or customer profiling and you want to see who in your database fits into these polygons? This is the filter you will want to use. The filter will determine whether or not your data falls within the bounding rectangle of the polygon. Allowing you to look at the data contained within the polygon only.     SQL: Updated Store Procedure: Stored Procedures in SQL are great, but sometimes you need to make small adjustments to them. This Update will allow you to choose the Parameter to update and use a field from your data to replace a specific part of the Stored Procedure.        
View full article
In-DB tools add a SELECT statement to Common Table Expression (CTE) queries.
View full article
The Run Command tool can be used to connect to SQL Server and run complex SQL statements as well as multiple stored procedures.
View full article
Connecting to an Oracle APPS schema causes Designer to get stuck in "Not Responding".
View full article
Error, "Error SQLPrepare: [Teradata][ODBC Teradata Driver][Teradata Database] The object name is too long in NFD/NFC." when connecting to Teradata.
View full article
Error opening "SHOW VARIABLES LIKE 'aurora_version'" when connecting to MariaDB via ODBC.
View full article
Internal Error in Field_Blob::GetAsSpatialBlob: Invalid SpatialBlob when using Spatial Object in SQL Server
View full article
Characters that are not on a standard English keyboard may need translation into Unicode or a language-specific code page for Designer and database drivers to read them correctly. Characters with incorrect encoding will often appear as boxes or question marks in the Designer Results screen and error messages. Unicode characters take more bytes than English ASCII characters. Changing the column type and increasing the column size may be needed. In Designer, the column size is the number of characters, not the number of bytes.
View full article
Let's talk about how to 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, but only if the key value in table#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 ) ;     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.           Example 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.   Example 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:     Please see the attached workflow: Where Exists Question.yxmd.
View full article
In SQL, you can join on a range, using code similar to the below snippet.   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   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.                                                                                                                                         For Advanced Joins: when a 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