Alteryx Designer Knowledge Base

Spatial Matching Best Practices Even though the Spatial Match tool is extremely fast and efficient, there are additional ways its speed can be further improved. This article provides suggestions to increase performance.     Spatial Relationship As defined in the Help documentation, the Spatial Match tool establishes the spatial relationship (contains, intersects, touches, etc.) between two sets of spatial objects. At least one input stream should include the Polygon type spatial objects. The other set will contain any of the other types of spatial objects, such as points or lines. But wait - which set of objects shall be used for the Universe (U) and which set for the Target (T)?   Under the hood the Spatial Match tool will put the U input into a temporary YXDB file with a spatial index . This is a highly efficient data format for spatial data. Thus, instead of indexing the geometric features of the object (first image), the objects' bounding boxes are indexed (second and third image). . https://postgis.net/workshops/postgis-intro/indexing.html This effectively means that when calculating a spatial match, only a few spatial objects inside the relevant boundary box must be considered for the spatial calculation.  Next, every object from the T input is spatially matched with the relevant objects of the U input.   In one line: The Spatial Match tool can ignore most Universe records that do not match the Target record. Using this fact to your advantage can greatly speed up your workflow. Deselect spatial objects not needed As with many other tools, the Spatial Match tool has a built-in Select tool enabling one to deselect columns that are not needed. While discarding unnecessary columns comes in handy to make data sets more readable, it can be a real performance improvement. Therefore, unnecessary spatial objects should be removed from the workflow. Unnecessary data consumes memory and takes away otherwise available resources.                       In the below example, toggling back the spatial object will increase the tool output from 7 kB to 757 kB.     Spatial tool output with unnecessary spatial object Spatial tool output without unnecessary spatial object Consider Using the Dynamic Input Tool In certain circumstances, using the Dynamic Input tool is quicker to perform a Spatial Match than using the native Spatial Match tool. Note: This can be only used for the spatial relationship 'Universe contains Target'.   To perform a spatial match using the Dynamic Input tool, select the spatial data file, then choose the second option: 'Modify SQL Query'. Select the latitude and longitude fields for the Universe object, and the spatial object field for the Target object. This SQL filter will only let through data that fall within the bounding rectangle of the polygon.   Harness the Power of Calgary Data and YXDB The YXDB (.yxdb) and Calgary DB (.cydb) data formats use spatial indexing. As explained above, this can give the workflow a major efficiency boost. Therefore, when possible it is strongly advised to import data from the above two DB types.   The second advantage is that they both enable you to leverage the spatial index. As defined in the Help documentation,  for Calgary use the spatial Calgary Join tool. If specifying a Calgary file, be aware that the Calgary spatial index uses 5 decimal places of precision for compression and speed. The yxdb spatial index uses 6 decimal places. This adds an additional round-off error of up to a maximum of 1.8 feet to Calgary indexes. In other words, it is possible that a point can be 1.8 feet inside of a polygon and yet still be found as "outside."     In summary, using the YXDB and Calgary DB data formats has the advantage of the highly efficient spatial indexing.     Use Integrated Tool Input in Spatial Tools For larger data sets, the option to Use Records from File or Database can be used for added speed. This also uses the spatial index and has the advantage that the entire dataset will not have to be read into memory for the workflow to start, as I/O is usually the biggest performance bottleneck for Alteryx.       References Spatial Match tool (Help documentation)  

Alteryx Designer comes with tools (based on both R and Python) to create and use predictive models without needing to write any code. But what if you've got custom models written in R or Python outside of Designer that you want to use in Designer, or vice versa?

Quick navigation for the Tool Mastery Series!

How to test if a maximum limit of connection data for an OCI driver was exceeded   An OCI error may occur when running a workflow from a network location with many OCI inputs: OCIEnvCreate Error: No error handle available to report exact OCI_ERROR.   Prerequisites   A Designer workflow with Input Data tools having OCI connections to an Oracle database.   Product - Alteryx Designer downloads.alteryx.com Product - Oracle Instant Client for validated versions see: Supported Data Sources Oracle download site: Oracle Instant Client downloads   Procedure   Decrease the total connection data that OCI driver in the Oracle Client has to process for the workflow. If the workflow is accessed from a network drive, try putting the workflow in a folder close to the root of the driver, rather than in a long folder directory, or move the workflow to your workstation instead of a network directory. If there are many OCI connections in the workflow, see if the same data could be imported with fewer connections.   The OCI driver is recording the network location of the workflow. Also, if the workflow contains multiple OCI inputs, the Oracle Client OCI driver appears to compile all of them together to create the workflow connections.    Common Issues   A maximum limit of connection data for an OCI driver can be reached In testing, we found that a maximum limit of connection data could be reached, and then the error would occur. The workflow would run fine with multiple OCI connections if the workflow was located on the workstation. However, when running the same workflow from a network drive, the length of the folder path would determine whether or not it would error out. Similarly, adding or removing more OCI inputs would have the same effect. A limit would be reached, and an error would occur beyond that limit.    Additional Resources   Oracle Call Interface Programmer's Guide OCIEnvCreate()  

How to correct Designer Event status if it does not update This article explains how to correct Designer event status if it does not update. In the Designer Workflow-Configuration Window, on the Events tab, there is a checkbox option: Enable Events. When selected, any items in the Events window should be enabled and run as configured. However, in Designer 2019.1.4, the status of events is not updated by the checkbox. Events may remain either enabled or disabled regardless of the checkbox setting.        Prerequisites Product - Alteryx Designer, known to occur in Designer version 2019.1.4 only   Procedure This issue is fixed in version 2019.1.6. As a workaround, right click the workflow .yxmd file and select the option to open in Notepad or Notepad ++.         This will open the XML view of the workflow. Find the section for Events by clicking on Ctrl + F and typing Events in the Find window. Set the Enable value to "True" or "False" to enable or disable the event. For example:             Additional Resources Events

The below is taken from the Tips & Tricks series presented at  Inspire 2016 . Special thanks to  Margarita Wilshire  and the  Customer Support  team for compiling these useful tips!

Issue:  Unable to connect to the Controller "X"   When trying to connect to your Designer's Scheduler, the following error is observed:   Unable to connect to the Controller "X". Please consult the Alteryx Scheduler FAQ in Alteryx Help for troubleshooting assistance.   You may also observe that the "My Computer" option is missing in the Schedule Workflow window:   Environment   Alteryx Designer Scheduler add-on Admin version   Cause   The Alteryx Service is not running.   Solution   Start the Alteryx Service. See "Start the Alteryx Service" article.   Additional Resources   Configure Desktop with Scheduler How do I schedule a Workflow using Alteryx?

When your Python libraries don't work the way they should in Python tool, restoring the tool to it's original state could be the solution. This article walks through how to restore Python libraries and the virtual environment associated with the Python tool.

How To: Start the Alteryx Service   The Alteryx Service powers the scheduling functionality of Alteryx. In order for schedules to be created or executed, the Alteryx Service must be running. Follow the guide below for different ways to start the Alteryx Service   Prerequisites   Alteryx Designer Admin version Scheduler add-on required Alteryx Server   Procedure A - Start from the Services Window   Open the Services snap-in window using one of the below methods: Search Services in the Start Menu Press the Windows+R keys to open the Run dialog, type services.msc, and press Enter Open the Control Panel click/tap on the Administrative Tools icon, and double click/tap on Services shortcut Right click on Alteryx Service and click Start   Procedure B - Start via System Settings   Open the Alteryx System Settings (can be found in the Start Menu by search "Alteryx System Settings" Click the Next button until you reach the end of the System Settings, then click Finish You will then see a "Please Wait" message that indicated the Service is attempting to start Please note that if the service fails to start for any reason, you will not receive any message to indicate the failure when starting the service this way   Procedure C - Start via Command Line   Open Command Prompt as an Administrator Type the following command that references your installation path. The below example is the default installation path: "C:\Program Files\Alteryx\bin\AlteryxService.exe" start   Additional Resources   Alteryx Service will not start: No suitable servers Error found in LastStartupError.txt Gallery Service failed to start in a timely fashion

UPDATE: After reviewing the decision to deprecate the C# (.NET) SDK, we are committed to the C# (.NET) SDK working as it currently does in Alteryx Designer and Server for at least the next two years. This change is reflected in the specified time frame below.   Hello #AlteryxAddicts,   tl;dr The C# (.NET) SDK will be deprecated in December 2021. Users currently leveraging the C# SDK for custom tools will need to convert their tools to a different backend (C++, Python, or macro) ahead of its deprecation. As part of this, the UI of the tool will also need to be reconfigured using the HTML GUI SDK, or macro if using a macro. FAQ What Is Happening? The C# (.NET) SDK will be deprecated in December 2021. See the 'Next Steps' section for more information. What is the C# SDK? A way for .NET developers to create their own custom tools for Alteryx. Documentation can be found here: [installdirectory]\Alteryx\APIs\SampleCode\DotNetCustomTools.pdf Why is this changing? Alteryx is putting more resources into improving the C++ and Python SDKs. Who's Impacted? Anyone who is leveraging the C# (.NET) SDK. Who's Not Impacted? Anyone who is not utilizing the C# (.NET) SDK. What should we use instead? The Python SDK, C++ SDK, or macro backend (https://help.alteryx.com/developer/current/BuildCustomTools.htm). Next steps? The C# (.NET) SDK will be deprecated in December 2021. Custom tools utilizing the C# (.NET) SDK might continue to work on Windows, but not all existing functionality will be available and you should consider converting the backend of your tools to something that will be supported, such as Python, C++, or macro. The UI of the tool will also need to be reconfigured using the HTML GUI SDK, or a macro (Interface tools) if using a macro. Links to backend options documentation: Python, C++, or macro. Links to frontend options documentation: macro or HTML GUI SDK documentation. Questions? Contact bearly@alteryx.com Alter Everything!

Installing a package from the Python tool is an important task. In this article, we will review all the possible functionality included with the Python method Alteryx.installPackages().

So you've installed the licenses to run Scheduler. Now what?

Performance tuning is an art.  Start sketching today!

A broad overview and introduction to what Decision Trees are, and how they work.

Scenario: You have multiple fields in your data that correspond to customer addresses.  Some customers may have more than one address listed in their record.  However, you want to whittle that list to one address per customer.  That one address is the first, non-null address found when moving sequentially across a set of fields that contain address data. For our example, we have three fields of data related to addresses: Address1, Address2 and Address3.  The preferred field of data to use is Address1.  However, if Address1 does not contain data, then we’ll use the data in Address2.  If both fields of Address1 and Address2 do not have data, then we’ll use the data in Address3.  The final output should contain a single list of the addresses highlighted in yellow in Figure 1.         Figure 1: The final output will contain a single list of the data highlighted in yellow.  Method 1: Write a Conditional Statement The most common approach to this type of problem is to craft a conditional statement to select data based on a set of criteria (in this case, order).  In the case of our data, it would look something like this: IF IsNull([Address1]) AND IsNull([Address2]) THEN [Address3] ELSEIF IsNull([Address1]) THEN [Address2] ELSE [Address1] ENDIF   However, what if I had 20 fields of addresses instead of 3?  Just getting that statement with three fields took me too long to write out!  If you do have 20 fields, you might want to start typing that Formula now….   IF IsNull([Address1] AND IsNull([Address2]) AND IsNull([Address3]) AND IsNull([Address4]) AND IsNull([Address5]) AND IsNull([Address6]) AND IsNull([Address7]) AND IsNull([Address8])...   You get the idea.  And now you’re thinking, “You’re going to tell me there’s a better way, right?!?”  Well, yes...I am!     Method 2: Data Manipulation An alternative method of solving this problem is to manipulate the data using the Transpose, Filter and Sample tools.  I’ll share some advice from @RodL here: “If you want to make something…truly dynamic, then the ‘best practice’ when you are dealing with an unknown number of columns is to ‘verticalize’ the data”.  In our case, we may know the total number of columns of address data we have; what we don’t know is which column the data we want is actually in.    Following @RodL’s suggestion, we’ll ‘verticalize’ the addresses using the Transpose tool.  This stacks the addresses for each customer in order of the fields in the table (Figure 2).  We’ll use the Client ID (or Record ID, if you’ve chosen to add one) as our Key Field and the fields that contain address data as our Data Fields.     Figure 2: All address fields per Record ID (or Client ID) are stacked vertically in order of the field sequence.   Since Null values are not usable records for our purposes, we’ll use a Filter to identify all the usable data (non-Null values).  Now that our usable data is stacked vertically in order of field selection, we can Sample the first record from each Record ID (or Client ID) group.  We’ll configure the Sample tool to identify the First N (where N = 1) Records from a group (Group by Record ID or Client ID).   Figure 3: Sample the first record from every Record ID or Client ID group.      After some data clean-up with a Select tool, we're left with a column of the selected address for each of our customers:          

So we’re now downloading all the network-shared documents we want thanks to instructions   posted on our Knowledge Base, and we’re on our way to mastering FTP in Alteryx. But what if we want to take it a step further? A lot of our users rely on FTP as a drop zone for datasets that are generated periodically (e.g. weekly, monthly, or quarterly datasets). We should then be able to schedule a workflow to coincide with those updates, automatically select the most recent dataset, crank out all the sweet data blending and analytics we have in our scheduled workflow, and proceed with the rest of our lives, right? Right. We can do just that, and with a little work up front, you can automate your FTP download and analysis to run while you’re enjoying the finer things in life. Here’s how in v10.1:

Question How do you convert a string with a $ sign into a number? Answer 1. First remove the dollar sign and commas out of the string 2. Convert () to a negative sign "-" 3. ToNumber() function   Input:     Output:     There are many ways to do this in Alteryx, here are a few:   Example 1   Formula Tool -  ToNumber(Replace(Replace(Replace(Replace([Data], '$', ''), ',' , ''), ')' , ''), '(' , '-'))     Example 2   Formula Tool - ToNumber(REGEX_Replace(REGEX_Replace([Data], "\$|,|\)", ""), "\(", "-"))     Example 3   RegEx Tool #1 - Replace \$|,|\) with Replacement Text "blank", then  RegEx Tool #2 - Replace \(  with Replacement Text "-" Select Tool - Change Type to Fixed Decimal         See attached workflow.

Alteryx provides product technical support for a period of eighteen (18) months after the Release Date of each Release Version, as outlined in the tables below.   NOTE: You can find product 'Release Version' details for all Alteryx Analytics Platform products by following the steps here.   Alteryx Designer & Server Release Version Detail Release Date End of Support (EOS) 2019.1.4.57073 2/13/19 8/13/2020 2018.4.3.54046 11/14/2018 5/14/2020 2018.3.5.52487 10/1/18 4/1/2020 2018.3.4.51585 8/27/18 2/27/2020 2018.2.6 8/16/2018 2/16/2020 2018.1.6 7/17/2018 1/17/2020 2018.2.5.48994 7/5/18 1/5/2020 2018.2.4.47804 5/31/2018 12/1/2019 2018.1.4.44311 4/2/2018 10/2/2019 2018.1.3.42973 3/6/2018 9/6/2019 11.8.3.40692 1/19/2018 7/19/2019 11.7.4.37815 11/28/2017 5/28/2019 11.5.1.31573 8/15/2017 2/15/2019 11.3.3.30523 7/14/2017 1/14/2019 11.3.2.29874 6/27/2017 12/27/2018 11.0.5.26351 3/21/2017 9/21/2018 11.0.3.25674 2/15/2017 8/15/2018 10.6.8.17850 7/12/2016 6/28/2018 10.6.6.17413 6/28/2016 12/28/2017 10.5.9.15014 5/5/2016 11/5/2017 10.1.7.12188 2/18/2016 8/18/2017 10.1.7.11834 2/4/2016 8/4/2017 10.1.6.11313 1/14/2016 7/14/2017 10.1.6.60263 11/30/2015 5/30/2017 Predictive Analytics 10/24/2015 4/24/2017 10.0.9.58949 9/26/2015 3/26/2017 10.0.9.58645 9/9/2015 3/9/2017 10.0.9.58529 9/3/2015 3/3/2017   Alteryx Connect Release Version Version Information Displayed in Product Release Date End of Support (EOS) 2018.4 v18-4-1-20181114.131138-git.3fd95347 11/14/2018 5/14/2020 2018.3.0  v18-3-7-20180814.115808-git.ae708379 8/27/2018 2/27/2020 2018.2.4.47804 v18-2-6-20180418.134811-git.67cf65dd 5/31/2018 12/1/2019 3.0.0_0c4cae69 v17-4-7-20180212.164240-git.0c4cae69 3/6/2018 9/6/2019 2.0.0_05b4e5c2 N/A 11/28/2017 5/28/2019 1.0.1.31573 N/A 9/12/2017 3/12/2019 1.0.0.31573 N/A 8/15/2017 2/15/2019   Alteryx Promote Release Version Version Information Displayed in Product Release Date End of Support (EOS) 2019.1.0 v2019.1.0 - Build f8ae666 2/13/19 8/13/2020 2018.4.1 v2018.4.1 - Build 167a0f1 12/4/2018 6/4/2020 2018.4.0 v2018.4.0 - Build 5926960 11/14/2018 5/14/2020 2018.3.1 v2018.3.1 - Build d88f0a6 10/1/2018 4/1/2020 2018.3.4.51585 v2018.3.0 - Build c14dfc7 8/27/2018 2/27/2020 2018.2.4.47804 v2018.2.0 - Build 593b5b29 5/31/2018 12/1/2019 2018.1.3.42973 v2018.1.0 - Build 2bfb396 3/6/2018 9/6/2019   Alteryx Analytics Product Compatibility Policy Alteryx is committed to ensuring that workflows created in legacy versions of Alteryx Designer will continue to work as expected with newer versions of Alteryx Designer. Alteryx will identify any known workflow compatibility issues. With the release of 11.7, a user may convert workflows created in a newer version of Alteryx Designer for use in an older version of Alteryx Designer. In this 'downgrade' scenario, a dialog appears asking the user to confirm or cancel the conversion process. Conversions performed in this manner are not fully tested for compatibility. For Alteryx Designer users who also use Alteryx Server, Alteryx is also committed to ensuring that in an upgrade scenario, legacy versions of Alteryx Designer are able to successfully connect to and use the newest Alteryx Server version. Therefore, it is always recommended to upgrade Alteryx Server first and the Alteryx Designer installations are upgraded thereafter. Alteryx will identify any known upgrade compatibility issues. If you experience any issues with an upgrade, please visit alteryx.com/support.   Alteryx Designer Predictive Tools Compatibility Policy Alteryx Designer users can install predictive tools for use with open source R, or Microsoft R. This table details the versions compatible with each release of Alteryx Designer.   Alteryx Designer Version Standard R Version RRE - Revolution R Enterprise (renamed as Microsoft R Server in 2016) Microsoft R Client (MRC) Microsoft R Server (renamed as Microsoft Machine Learning Server in 2018) Microsoft Machine Learning Server (MMLS) 2019.1 3.4.4 Deprecated by vendor - Not supported 3.4.3 Deprecated by vendor - Not supported 9.3 2018.4 3.4.4 Deprecated by vendor - Not supported 3.4.3 Deprecated by vendor - Not supported 9.3 2018.3 3.4.4 Deprecated by vendor - Not supported 3.4.3 Deprecated by vendor - Not supported 9.3 2018.2 3.4.4 Deprecated by vendor - Not supported 3.4.3 Deprecated by vendor - Not supported 9.3 2018.1 3.3.2 Deprecated by vendor - Not supported 3.3.2 9.0/ 9.1 NA 11.8 3.3.2 Deprecated by vendor - Not supported 3.3.2 9.0/ 9.1 NA 11.7 3.3.2 Deprecated by vendor - Not supported 3.3.2 9.0/ 9.1 NA 11.5 3.3.2 Deprecated by vendor - Not supported 3.3.2 9.0/ 9.1 NA 11.3 3.3.2 Deprecated by vendor - Not supported 3.3.2 9.0/ 9.1 NA 11.0 3.3.2 Deprecated by vendor - Not supported 3.3.2 9.0/ 9.1 NA 10.6 3.2.3 8.0 Not supported NA NA     Alteryx Analytics Platform Hardware and Operating System Requirements Product Chip Disk Size Machine OS Processor RAM Designer Quad core (single chip) 500GB - 1TB Minimum: 64-bit, High Performance: 64-bit, 32-bit not supported Microsoft Windows 7 or later (64-bit) 2.5GHz or faster 8GB;16GB Connect Quad core (single chip) 100GB Recommended: 64-bit, 32-bit not supported Microsoft Windows Server 2008R2 or later 2.5GHz or faster 8GB Promote Quad core (single chip) 100GB Required: 64-bit, 32-bit not supported Linux CentOS 7 2.5GHz or faster 16 GB Server Quad core (single chip) 1TB Recommended: 64-bit, 32-bit not supported Microsoft Windows Server 2008R2 or later 2.5GHz or faster 16GB;32GB   Supported Browsers for Alteryx Analytics Platform   Alteryx Analytics is supported on the following versions of Chrome, Safari, Firefox, Internet Explorer and Edge web-browsers*:       Chrome - Windows Safari - MacOS Firefox Standard Release Internet Explorer Edge Alteryx Analytics Platform latest 11 and above latest (60 and above for Firefox ESR) 11 and above latest         *Note: from time to time, the vendor may provide an update to its web-browser that results in compatibility issues with the browser version used by Alteryx in the development and pre-release testing of the Alteryx platform. Alteryx will use reasonable efforts to mitigate against this risk but we also encourage you to be mindful of browser versions used within your organization and to manage your deployment of the Alteryx platform accordingly.   Alteryx Virtual Environment Support Nutanix, Azure, AWS, VMWare, GCP. All of Alteryx's products run in modern virtualized environments that are properly configured. Support also depends on using a supported and properly configured Windows operating system that also meets or exceeds minimum OS hardware environments. Alteryx Server MongoDB Support Policy Alteryx Server users have two options for managing user and instance metadata. 1) Use the version of MongoDB embedded in the server installation 2) BYO (Bring Your Own) Mongo DB instance Alteryx ships a version of MongoDB in product <OR> customers can connect to their own version. In the case of the latter, Alteryx doesnt provide support, but we do ensure that users are able to successfully connect to their own existing instance of MongoDB and the expectation is that users will be able to connect to that MongoDB instance and manage their Server users and metadata from there.   Release Version Release Date End of Support (EOS) Version of MongoDB embedded Certified versions of MongoDB supported to connect to user managed instances 2019.1.4.57073 2/13/19 8/13/2020 MongoDB version 3.4 MongoDB version 3.0 and 3.4 2018.4.3.54046 11/14/2018 5/14/2020 MongoDB version 3.4 MongoDB version 3.0 and 3.4 2018.3.5.52487 10/1/18 4/1/2020 MongoDB version 3.4 MongoDB version 3.0 and 3.4 2018.1.6 7/17/2018 1/17/2020 MongoDB version 3.4 MongoDB version 3.0 and 3.4 2018.2.5.48994 7/5/2018 1/5/2020 MongoDB version 3.4 MongoDB version 3.0 and 3.4 2018.2.4.47804 5/31/2018 12/1/2019 MongoDB version 3.4 MongoDB version 3.0 and 3.4 2018.1.4.44311 4/2/2018 10/2/2019 MongoDB version 3.0 MongoDB version 3.0 2018.1.3.42973 3/6/2018 9/6/2019 MongoDB version 3.0 MongoDB version 3.0 11.8.3.40692 1/19/2018 7/19/2019 MongoDB version 3.0 MongoDB version 3.0 11.7.4.37815 11/28/2017 5/28/2019 MongoDB version 3.0 MongoDB version 3.0 11.5.1.31573 8/15/2017 2/15/2019 MongoDB version 3.0 MongoDB version 3.0 11.3.3.30523 7/14/2017 1/14/2019 MongoDB version 3.0 MongoDB version 3.0 11.3.2.29874 6/27/2017 12/27/2018 MongoDB version 3.0 MongoDB version 3.0 11.0.5.26351 3/21/2017 9/21/2018 MongoDB version 3.0 MongoDB version 3.0  

How to Trim Numbers with a RegEx formula

Is there a  way to avoid the Cross Tab Tool from transferring all the input information alphabetically? Simply add a RecordID to your records and add the RecordID field as a grouping field in your Cross Tab Tool to keep the order!