Alteryx Designer Knowledge Base

Quick navigation for the Tool Mastery Series!

How To: Create a Calgary Database   A Calgary Database is a proprietary Alteryx format that allows users to query against a file of millions of records quickly without having to read in all the data. A Calgary Database is created with the Calgary Loader tool, which allows users to create a database from any type of input while selecting which fields to index. Calgary Databases are useful for running ad-hoc queries against a large dataset, e.g. ConsumnerView data.     Prerequisites   Alteryx Designer (any version)     Procedure   Bring the data to be written to the Calgary Database into Alteryx and transform it until it is in the desired format, keeping in mind opportunities to standardize values to make the indexes work better. For example, are all your ZIP codes properly and consistently formatted? Starting with Alteryx 5.0, Calgary Indexes are not case sensitive anymore, treating "CALIFORNIA" and "California" the same. However, if some of your data uses the full state name and some uses the state abbreviation, and you are planning on using state as an index, you should pick one and use it consistently. You might also want to add flags that other users might find useful for querying data. For example, create a flag to indicate the current month's (or quarter's or year's) data or a custom region such as "NorthEast", "South", "Midwest", etc. In the Calgary Loader tool, map the location of the Calgary Database in the "Root File Name" box. The tool will create a .cydb file (the data file), multiple .cyidx files (the index files), and an __Indexes.xml file that contains the index values. Since it will write out multiple files, a best practice is to have a folder dedicated to your Calgary Database. You cannot append to a Calgary database. To add records, rebuild the Calgary database. Use the "Data" and "Index" columns to select which fields to include as data fields and which to index. Typically, all fields are included as data, but only certain ones are indexed as each index takes time to create. For index fields, the index type can be selected. "High Selectivity" is used for data with many different possible values, such as ZIP codes. Select "Low Selectivity" for data with fewer unique values, such as State or Region. "Low Selectivity" also creates a drop-down option for the Calgary Input Tool. By default, the index type setting is "Auto". In Auto mode, Alteryx looks at the first 1 million rows of data and decides if the index should be high or low selectivity. All fields with more than 550 unique values will be set to high selectivity. If the data changes after the first 1 million rows, Alteryx might select the incorrect index type. This option might also take longer to process since Alteryx has to look at 1 million rows of data for each index in Auto mode. Use the Calgary Input tool to read in data from a Calgary database as described here: Querying a Calgary DB / File to Select and Limit Input Records. Did you know that you can read in the .cydb file the same as a .yxdb file in the regular input tool? However, you won't be able to query any of the indexed fields.     Additional Resources   Using Custom Lists to query Calgary Indexes in Apps and Macros Querying a Calgary DB / File to Select and Limit Input Records Building a Calgary Database with "Searchable" Fields Calgary Databases

Now you can use the Email tool with G-Suite!

Fix "Error: package or namespace load failed for 'AlteryxRDataX'"   Following an new installation of the predictive tools, while trying to run a workflow involving R tool or any macros that use R tool (most of the predictive tools for example), you may face following error message:         Error: R (1): Error: package or namespace load failed for 'AlteryxRDataX': Info: R (1): .onLoad failed in loadNamespace() for 'AlteryxRDataX', details: Info: R (1): call: NULL Info: R (1): error: package or namespace load failed for 'showtext': Info: R (1): .onLoad failed in loadNamespace() for 'showtext', details: Info: R (1): call: utils::unzip(font_file, exdir = out_dir, overwrite = FALSE) Info: R (1): error: 'exdir' does not exist Error: R (1): Error: Unable to load the AlteryxRDataX package - Use the R installer provided by Alteryx Error: R (1): Execution halted         Example:     Environment   Product - Alteryx Designer All versions Product - Predictive tools All versions   Cause   This error occurs if of the R packages used by Alteryx for the R tool (https://cran.r-project.org/web/packages/showtext/index.html), can't freely access the folder defined by environment variable TMPDIR, TMP or TEMP. Package will stop as soon as one of them could be find found. They will be checked in this particular order, with user variable taking precedence over system variables if the 2 are defined).   Example: In the following, example, environment variables will be read in following order (first available folder will be used):   system TMP user TEMP system TEMP                 As C:\Windows\Temp exists but it not accessible for current user (see below), error will occur:       Solution   Close Designer Open Control Panel Open System Open Advanced system settings Click on Environment Variables Create a user environment variable called TMP or (TMPDIR if TMP already exists) with a value set to a fully available folder (example: %LOCALAPPDATA%\Temp). Click on OK to close the window.     7. Start Designer and run your workflow or test again      

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().

How To: Enable Microsoft Excel Binary, Excel Legacy, Access (accdb extension) for In/Out tools   The following formats may not be found in the "File Format" option in the In/Out tools and require a separate installation from Microsoft: Microsoft Excel Binary (.xlsb) Microsoft Excel Legacy (.xlsm) Microsoft Access (.accdb)   Note that these drivers do not come with our installation but from Microsoft Office or separate driver installation.   Prerequisites   Product - Alteryx  Designer   Procedure   To download the driver, go to https://www.microsoft.com/en-us/download/details.aspx?id=13255 Follow the instructions to download the installer.  Run the installer. After installation, you should be able to find the Excel Binary, Excel Legacy and Microsoft Access (.accdb) options in the Input Data tool and the Output Data tool.   

Troubleshooting Invalid Path: "Cleanse.yxmc" / Nested Macro Locations are not supported   Randomly Designer appears to be losing macro references. This results in the below two behaviours.   Invalid path error for macro based tools   In some cases the user will see the error message below when running a workflow containing the Data Cleansing tool. Typically a simple uninstall will not solve the issue.   Invalid Path: "Cleanse.yxmc"   Additionally, the error message below will be shown when trying to use the Publish To Tableau macro.   Invalid Path: "PublishToTableauServer_v2.0.0\Supporting_Macros\PublishToTableauServer.yxmc"                                           Unable to add Macro paths in User Settings   When trying to add a path to a macro this will lead to the below error.   Nested Macro locations are not supported   Environment   Alteryx Designer   Cause   This is caused by a corrupted AddOnData folder in %ProgramData%\Alteryx\DataProducts.   Solution   1. Close Designer 2. Rename the   AddOnData   folder in   %ProgramData%\Alteryx\DataProducts 3. Restart Designer (to recreate the AddOnData   folder)   As always if you encounter issues please feel free to reach out to   Alteryx Customer Support.

How to use datetime functions in the Formula tool.

Note: This article is out of date. CLICK HERE for up-to-date, single source help on magnification, mouse keys, mouse, and keyboard shortcuts in the Alteryx Designer product. There are many different keyboard shortcuts to help you in your task of building modules in Alteryx.  As in most software packages, the standard set of keyboard functions native to the Windows operating system also apply to Alteryx.   Handy shortcuts specific to Alteryx: Ctrl + Alt + B : Show/Hide Toolbar Ctrl + Alt + T : Show/Hide Toolbox Ctrl + Alt + V : Show/Hide Overview Ctrl + Alt + R : Show/Hide Results Window Ctrl + Alt + C : Show/Hide Configuration Window Ctrl + Alt + D : Show/Hide Interface Designer Ctrl + Shift + B : Add all Browses after selected tools Ctrl + R : Run Workflow F5 : Refresh Configuration Ctrl + Shift + - : Align selected tools horizontally Ctrl + Shift + + : Align selected tools vertically Ctrl + Arrow Key : Nudge tool by one pixel The full list of keyboard shortcuts can be found here.   Canvas Navigation was updated in Alteryx Version 10.1. Check out the blog posted here. Standard Shortcuts Ctrl + N : Open a new workflow Ctrl + O : Open an existing workflow Ctrl + Z : Undo Ctrl + Y : Redo Ctrl + F : Find Tool (this allows you to find tools in your workflow by name or number) Ctrl + X : Cut selected (in the canvas this will cut the selected tools, text will be cut if the text is selected inside the tool's configuration window) Ctrl + C : Copy selected tools (in the canvas this will copy the selected tools, text will be copied if the text is selected inside the tool's configuration window.  Also, you can select specific rows from a browse tool and copy those rows using this command) Ctrl + V : Paste tools (if rows have been copied from a Browse tool this command will create a Text Input on the canvas of copied rows from the clipboard) Ctrl + A : Selects all tools in the workflow Ctrl + S : Save your module (SaveAs if this is the first time the module is being saved) F1 :This single button will bring up the help file specific to the tool you have selected on the canvas, otherwise it pulls up the generic help window. Ctrl + Tab : Switch between open workflows (if you have more than one workflow open in a single session of Alteryx)   Other commands and Handy Shortcuts: Right Click and Drag : Inside a select tool, or any tool with  "Select" functionality, you can select a field, or group of fields, and right click and drag those fields to a new destination in the field structure.  This eliminates needing to click the Up/Down arrows to reorder fields. Hold down the mouse wheel : Pan the canvas Space Bar + Left mouse click : Pan the canvas Shift + mouse wheel : Scroll canvas horizontally Ctrl + roll mouse wheel : Zoom the Canvas Ctrl + Arrow Key : Nudge tool by one pixel Splash Screen : To remove the splash screen while the program loads simply click on the image Open one or more files : Drag files from Windows Explorer directly to the canvas. Workflow files (*.yxmd, *.yxwz, *.yxmc) will open directly in a new tab on the module canvas. Data files will be represented by a configured Input Tool for each data file on the active workflow.

Fact: workflows are the best. Look it up. They’re all about getting things done and, with hundreds of tools and the ability to integrate external processes , there’s no shortage of things you can get done. We know that there are some areas of analytics that require a little extra firepower, however, and that’s why you can leverage your workflows in apps and macros for added functionality.

How To: Add Exception Handling to Oracle Pre or Post SQL   Exception handling may be needed so that a scheduled workflow continues even if a pre or post Oracle SQL statement has an error. Since PL/SQL currently is not supported in the SQL Editor, you will need to create a stored procedure for exception handling and call the stored procedure in the pre or post SQL section.   This example shows how to create a stored procedure that handles exceptions for a delete table statement when the table was previously removed and does not exist. (Oracle currently does not have an IF EXISTS option.)   Procedure   Adding a parameter for the table name is recommended so that one stored procedure can be reused for any table.   Note: You will need to create procedure permission on the Oracle database, contact a DBA if you are lacking this permission.    Use this format in your SQL editor for the stored procedure. Notice a space is added at the end of the SQL command ‘DROP TABLE ’ followed by || for concatenation and the parameter name used as a place holder for the table name.      Once the stored procedure exists on the database, you can reference it in the Pre SQL or Post SQL sections of the Input Data or Output Data Tool. Click on the Ellipsis button to open the section.      On the Stored Procedures tab, just highlight the stored procedure name on the left, and then in the Value text box, type in the table name surrounded by single quotes.     Alternately, you can call the stored procedure from the SQL Editor tab, using this format:     Additional Resources   Input Data Tool  Output Data Tool   

This article details different methods for connecting to an Oracle database from Alteryx. 

The  Append Fields  tool adds  every  row of the source input to   every  row of the target input as new columns. This is also known as a Cartesian or cross join!

The Input Data Tool is where it all starts in the Designer. Sure, you can bring in webscraped or API data with the Download Tool (master it here ) and our prebuilt Connector Tools , but the tool that makes it a breeze to grab data from your most used file formats and databases is the Input Data Tool.

How to get started with using Iterative Macros   If you are new to working with macros in Designer, a good place to begin is with this article: Standard Macros. It covers the basics of working with macro inputs, configuring variables that can be updated by the user, adding a macro to a workflow, and saving a macro on a Designer Toolbar.   For some fun and interesting ways to conceptually understand the differences between Standard, Batch, and Iterative Macros, I suggest these articles from the Macro Maynia Blog series:    Macro Maynia: Knitting  Macro Maynia: Loads of Fun  Macro Maynia: A Piece of Cake    This article will help you get started with using Iterative Macros. There are examples, recorded training sessions, and other articles to further your knowledge listed in the Common Issues and Additional Resources sections.   An iterative macro is a process that runs a specified number of times or until a defined condition is met. This type of macro adds a global workflow variable called IterationNumber. This variable is useful in workflow functions, it increments by 1 each time the macro runs. An Iterative Macro is similar to an SQL or Python while statement, in that the macro continues to run while a condition remains true.     An iterative macro is useful when processing records in a specific order, such as by priority or spatial distance. You can implement functions so that only records with a priority number matching the iteration number process on any run of the macro (for example, priority 1 records for the first run of the macro, followed by priority 2 records next, and so on).  Similarly, you could determine a driving route by selecting the nearest destination to the last starting point on each iteration, creating a specific sequence.   Iterative macros can be helpful after grouping your data into numeric sets. For example, the Multi-Field Binning Tool or Tile Tool could be used to create subsets of your data with the records in each subset having the same number. Afterward, the iterative macro would run only the group of records with the tile or bin number matching the iteration number, and the macro would run one time for each group number until all the groups are completed.    Here is an example of an iterative macro that assigns items from warehouse stock to retail stores based on the store’s priority. If the comments are too small, try holding down the Ctrl key and click on the plus key or scroll up with the mouse wheel to zoom in (minus key or scroll down to reverse the effect).     This macro will loop until all stores are processed, or the maximum number of iterations set on the Interface Designer Properties tab is met. Keep in mind that an iterative macro needs a true/false test. In this case, the macro will keep looping as long as the test of an existing record with a priority matching the iteration number is true. As soon as no more records have a priority matching the iteration number, the macro will stop sending records out of the iteration output, and the macro will stop running.   Tip: It is helpful to use the Output Name field in the Macro Output Configuration screen. Add a name for the output that will iterate back through the macro.     Afterward, you can easily pick the correct output on the Interface Designer screen. You can also set the maximum number of iterations, the action to occur when the maximum reached, and the Output Mode. For best practices, you should keep the maximum number of iterations as low as possible. If there are more iterations than expected, it may be time to check the logic in the macro.       Common Issues   You may need some sample workflows with macros to help you get started. Sample macros for different use cases are available in Designer by going to Help, Sample Workflows, Use scripting and automation tools, and Build a Macro. When viewing the workflow, right-click on the macro tool, and then select the option to Open Macro. This will open the macro workflow packaged for that tool on a separate tab.   Also, there are recorded training sessions for macros available on the Community website here: Recorded live training label: Macros.   Additional Resources   Tool Mastery Apps and Macros Iterative Macro Help Page  Iterative Macro: Collatz Conjecture Example CS Macro Dev: Iterative Macros Getting Started With Batch Macros  CS Macro Dev: Batch Macros 

How to get started with using Batch Macros   If you are new to working with macros in Designer, a good place to begin is with this article: Standard Macros. It covers the basics of working with macro inputs, configuring variables that can be updated by the user, adding a macro to a workflow, and saving a macro on a Designer Toolbar.   For some fun and interesting ways to conceptually understand the differences between Standard, Batch, and Iterative Macros, I suggest these articles from the Macro Maynia Blog series:    Macro Maynia: Knitting  Macro Maynia: Loads of Fun  Macro Maynia: A Piece of Cake    This article will help you get started with using Batch Macros. There are examples, recorded training sessions, and other articles to further your knowledge listed in the Common Issues and Additional Resources sections.   Learning how to configure a control parameter is the most important part of working with batch macros, as the control parameter is the defining part of a batch macro. When adding a Control Parameter Tool to the canvas, the workflow automatically becomes a batch macro.   A batch macro runs one time for each record in the control parameter file. An output is generated each time, and a union of the outputs is created.   A Batch Macro can group data into batches if needed and process one batch at a time. For example, you can have a column of values in the control parameter file that dynamically changes the value used in the macro for each batch that runs.   In the example below, the Control Parameter Tool is used to control the number used as a multiplier in the Formula Tool. The Action Tool is set to replace the placeholder value of 4 as the number used for multiplying with the value coming from the Control Parameter tool.     After saving the macro, and adding it to the workflow, there is an input with an upside-down question mark for each Control Parameter Tool used in the macro.     For this example, the Control Parameter file is on the left. It has a batch column that can be used for grouping and a multiplier column. The input is on the right, and it has a corresponding batch column that matches up for grouping with the Control Parameter file and the Number column that will be multiplied.                           When selecting the macro icon in a workflow, there is a Group By tab. If grouping fields are used, a batch macro acts in a similar way as an inner join between the control parameter file and the input file, regarding the number of records affected. A batch macro will, of course, do more than joining columns, but the number of records affected will be the same as an inner join. The value in a control parameter field and the macro processes are applied only to input records that match on the columns selected in the Group By tab.     Each of the 5 batches in the Control Parameter file matches with 2 records in the Text Input file for a total of 10 output records.     When no fields are selected in the Group By tab, the Control Parameter acts in a similar way as an append or Cartesian join to the input data. You can think of it in this way as each record in a control parameter field and the macro processes are applied to each record in the input file. The number of affected records is the same as an append. However, a batch macro is used for more than an append to existing data.      Since there are 5 records in the Control Parameter file and 10 records in the Input, there is a total of 50 output records.     When clicking on the macro icon, there is a Questions tab that allows you to map the input fields from your workflow to the fields in the Macro Input Tool and select a field for each Control Parameter Tool added in the macro.     A batch macro also as a workflow global variable called IterationNumber that corresponds to the number of times a macro has run. This variable can be used in functions to ensure a specified sequence is followed by the macro when processing records. From more information on using the IterationNumber variable see: Iterative Macros.    Common Issues   You may need some sample workflows with macros to help you get started. Sample macros for different use cases are available in Designer by going to Help, Sample Workflows, Use scripting and automation tools, and Build a Macro. When viewing the workflow, right-click on the macro tool, and then select the option to Open Macro. This will open the macro workflow packaged for that tool on a separate tab.   Also, there are recorded training sessions for macros available on the Community website here: Recorded live training label: Macros.   Additional Resources   Tool Mastery Apps and Macros  Batch Macro Help Page CS Macro Dev: Batch Macros   Getting Started With Iterative Macros  CS Macro Dev: Iterative Macros   

How to get started with using Standard Macros   When you have a repetitive task to complete processing data across different columns, data sources, and/or workflows, a Standard Macro may help automate that task.   If you notice a part of your workflow has become a series of the same tools being reused multiple times like the example below, it may be time to consider using a macro.      This article will help you get started with using Standard Macros. There are examples, recorded training sessions, and other articles to further your knowledge listed in the Common Issues and Additional Resources sections.   You can think of a Standard Macro as a shared process that can be reused for different fields, data sources, or workflows. It packages a process completed with a set of tools as a single tool and creates one output.   A Standard Macro can be configured with questions that the user answers to dynamically configure the macro before running the workflow. By answering these questions, the user can update the values used.   The macro is saved as a separate file with a .yxmc extension, and it can be shared for use in other workflows. The set of tools in a Standard Macro is run once and then it is done.     The macro type is selected on the Workflow tab in the Workflow - Configuration window.     There are a few basic steps to configuring a Standard Macro. Select a Macro Input Tool from the Interface Toolbar to start. When adding a Macro Input Tool to the canvas, the workflow type will automatically change from Standard Workflow to Standard Macro.   Placeholder input should be added to the Macro Input Tool for developing and testing the macro. The placeholder data in the Macro Input Tool will be replaced by the workflow data once the macro is complete.   You can either create test data in with the Text Input option or select a File with the data you want to use. This test data should have the same number of columns as the workflow input that will run through the macro and the same data types.   Notice in the Macro Input Configuration screen that the Show Field Map checkbox is selected. This option will allow the user to click on the macro icon once it is added to a workflow and select the columns that will map to the fields used in the Macro Input Tool as placeholder data.     The Anchor Abbreviation in the screen capture above is set to 1. It will appear on the matching anchor for the macro when it is added to the workflow. For example, here is a Standard Macro with inputs 1 and 2 as well as outputs 3 and 4.    Once you have your macro input configured, you can create the process that you want to automate. Here a new column is created that adds 5 to the values coming from the Macro Input tool.   Keep in mind that you can configure the macro with questions, and have the user set the values used in the macro.  In this example, a Text Box Tool was added. When the Q output anchor of the tool is connected to the lightning bolt anchor of another tool, an Action Tool automatically appears. This tool gives the user the ability to define the Action that will occur with the user’s answer to the question.     In this example, the Action Tool is going to update the Formula, and replace the number 5 with the number typed in by the user.     Once the macro is completed and saved as a .yxmc file, it can be added to a workflow as a tool selected from the Insert Tool Menu. The record values from the workflow will replace the values in the Macro Input Tool.   To add a macro to a workflow right click on the canvas and select Insert Tool, then look for the Macro option at the bottom.     If the Show Field Map option was selected in the Macro Input tool, the user is prompted to map the field(s) from the workflow to match the placeholder data in that tool. Since a Text Box Tool was added in the macro and set to replace a number in the Formula Tool, the user is also prompted for the number that will be used in the addition function to produce the result. The prompt is like a question from the Text Box Tool asking what number to use for replacing the placeholder value in the Formula Tool.     The Standard Macro can be a superhero in your workflow and make it easier to complete. You can customize your macro with a picture by going to the View menu and selecting Interface Designer. Next, click on the Properties icon and select the Custom Icon option.      The Interface Designer is also helpful for arranging the questions shown to the user and debugging the macro. For details see: Interface Designer Part 1.   Common Issues   You may need some sample workflows with macros to help you get started. In Designer, try going to the Help menu, then Tutorials, and select the Intro to Macros option. There is a two-part workflow lesson for beginners. Also, sample macros for different use cases are available in Designer by going to Help, Sample Workflows, Use scripting and automation tools, and Build a Macro. When viewing the workflow, right-click on the macro tool, and then select the option to Open Macro. This will open the macro workflow packaged for that tool on a separate tab.   There are recorded training sessions for macros available on the Community website here: Recorded live training label: Macros.   Once you have started creating macros, you may want to organize and access them in a Designer Toolbar for easy reuse.   You can have Designer automatically add macros to a Macro toolbar through User Settings. Go to Options, User Settings, Edit User Settings, and select the Macros tab, then click on the plus sign button. You can add a folder location in the Search Path. Any macros stored in this location will appear in a toolbar category titled Macros that is added by Designer.     If you want your macro to appear in other existing Toolbars, use these instructions: Macro Installation.   Additional Resources   Macro Help Page Macro Input  Macro Output  Tool Mastery Apps and Macros  Best Practices for Sharing Macros  Getting Started With Batch Macros  Getting Started With Iterative Macros   

How to prepare two inputs for Fuzzy Match Merge Mode   Here is a quick reference guide that will help you prepare two different data sources for use with Fuzzy Match Merge Mode. This mode only compares data from different sources, and it is often used to merge new data back with the primary data source. The Fuzzy Match Tool accepts only one input, so you will need to prepare the data first.   Join the data together, results that match exactly will go through the J output anchor and do not need fuzzy matching.   The remaining data from the L and R output anchors will need record ids for matching. Add record IDs for the first input.   Use the Formula Tool to create a new column for each input showing the source. A function with just a text string in quotes will add that text to each record. Using Merge Mode, the Fuzzy Match Tool will only compare records with a different source.   Ensure the correct alignment of the columns in the Union Tool by using the Manually Configure Fields option.   Sort by the Source column so that all the Input A records with a record ID are placed first in the list.   Record IDs for the 2nd input are added after the join using an expression so that the IDs automatically start sequentially after the number of records in the 1st input.   In the Fuzzy Matching configuration screen, use the new Record ID and Source ID fields, as well as the fuzzy match fields that were stacked together with the Union Tool.     Here you can see all fields needed for Fuzzy Match Merge Mode Configuration are available.      The example workflow is attached. Once the Fuzzy Match merge is complete, there are many options for completing the workflow, such as adding a Unique Tool to remove duplicates and joining the matching records back with the original data. Please see the articles and training videos in the Additional Resource section for examples and more information.   Additional Resources   Tool Mastery Fuzzy Match  Fuzzy Match Tool Alteryx Help Page   Alteryx Academy video training session: Fuzzy Matching for Beginners  Alteryx Academy video training session: Fuzzy Matching Intermediate Users 

How to register and create Dynamics CRM Application on Azure

If you are installing two different versions of Alteryx Designer and their respective predictive tools- there is an order of operation.    1. Download and Install the Administrative Version of Alteryx Designer. 2. Download and Install the Administrative Version of Alteryx R/Predictive tools. 3. Download and Install the Non-Administrative Version of Alteryx Designer. 4. Download and Install the Non-Administrative Version of Alteryx R/Predictive tools.