Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
When publishing a workflow to Gallery or Scheduler (Designer + Desktop Automation) or when packaging a workflow for export, checking the boxes for what to include and what to exclude seems to work inconsistently as of Designer 2020.2. Here is a workaround to cover you until the issue is resolved in a future release.
View full article
User is getting the error "Detour tools in macros must terminate within that macro" when trying to use a macro in a workflow
View full article
Support policy for CReW Macros.
View full article
Custom tool not showing in tool pallette after installation.
View full article
Troubleshooting the error: XmlParse Error (2): the element Configuration is not properly closed.
View full article
"I have a bajillion files that I need to bring into my workflow... wayyyyyy too many for me to use individual Input Data tools! What's the best way to do this?"                    - every Alteryx user ever
View full article
Some Excel files, particularly those created by third-party programs, are encoded differently than Alteryx expects. In many cases, such files can be opened in Excel and then saved, resulting in files that Alteryx can open. Please refer to Designer Knowledge Base article entitled "How to check for encoding or formatting issues with Excel worksheets" for diagnosis tips.
View full article
I know I set this right, but it's not doing what I want it to!
View full article
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.
View full article
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 
View full article
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   
View full article
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   
View full article
The partnership between Alteryx and Tableau is becoming stronger and stronger, and the seamless effortless integration has been made easier through the Publish to Tableau Server Tool. This article demonstrates the use of the Publish to Tableau Server tool, available on the Alteryx Analytics Gallery.
View full article
The TS Factory tools can build time series models and forecasts for multiple sets of historical data groups, without the need to separate them.  This is their relationship to the batch macro.
View full article
Some time ago, there was a nice writeup: The Ultimate Alteryx Holiday gift of 2015: Read ALL Excel Macro: Part 2. This amazing macro allowed me to read any excel file, regardless of the number of tabs.   Until I start working with users that use diacritics on the sheet names.     For example:   If we try to use the mentioned macro, you will receive an error like this: ‘not a valid name’ I decided to approach this as a macro (2 levels) and use the Directory functionality to read all possible xls files within a folder.   Level 1: Read xls Tabs   This macro will read all the tabs for a single xls file. I used an R tool that includes the library readxl.   This library allows us to read xls files. I used the excel_sheets function to extract the sheet name and compile the sheet name with the name file path. You will receive a column per tab that the xls file has. I cleaned these two values and passed them as Path and Tab.     This data is sent to the Read xls file macro.     Level 2: Read xls Files   This macro gives structure to the full path (Path + Tab) using the structure needed in xls files. It uses the Dynamic Input tool to dynamically choose the data and output its content.       Note: Update the XXXX for your corresponding paths Don’t forget to install the R library readxl under your %Program Files% path g. C:\Program Files\Alteryx\R-3.5.3\library
View full article
So we’ve generated our list of Key Values (in Part 1) to be read by the Tree Interface tool and when we look at the Tree Interface it looks correct.   Figure 1   So how can we use these selected values in a workflow? As a review, our original data consists of very pretend pet data.   Figure 2   Now we need to associate the Key Values to our data so that our data can be queried.  This can easily be done by adding a couple steps to the workflow we create in Part 1  (updated workflow is attached).   By adding a Transpose tool, the table can be manipulated to get each possible Key Value for each record. Then a Select Tool to rename the Value field to Key. Those results are saved to a new file (Pet data with key values.yxdb) that will be used in the app.   Figure 3   When values are selected in a Tree, a list of the Key Values are returned with a line break between each value.  In the above Tree Interface example (Figure 1) the Key Values 111 and 26 are returned as a string data type and would look like this:    Figure 4   Setting up the App We now have all the parts we need to create the app: Pet Key values.yxdb (from Part 1) and Pet Data with Key Values.yxdb   The start of the app is a Tree Interface tool that points to the Pet Key Values.yxdb file, and an Input tool pointing to the Pet Data with Key Values.yxdb file.   Figure 5   We know the Tree Interface returns a list of Key Values, so we can then filter our pet data on the Key Values.  A Filter tool is added with a template filter that will ultimately be updated by an Action tool, once we connect the Tree Interface to the Filter tool.   Figure 6   Now let’s connect the Tree tool to the Filter tool and configure the Action tool.   Figure 7   We are going to select the Action Type ‘Update Value with Formula,’ and update the full Expression. Now for the Formula: click on the ellipsis (…) to add this formula:   '[Key] in ("' + REGEX_Replace([#1], '\n', '","') + '")'   Figure 8   This expression will build an IN statement (Figure 5) based on the selected values from the Tree Interface.  If the returned values from the Tree tool are, as in Figure 4:   111 26   The expression will build a string starting with:  [Key] in (“   The REGEX expression looks for all line breaks (or newlines) in the connection 1 ([#1]) value, denoted by the \n, and replaces those occurrences with: “,”   And then finishes the expression with: “)   The final expression looks like this: [Key] in (“111”,”26”)   This will replace our template expression when the app is run.   To test your Action tool expression, use the Interface Designer’s Debug tool.  Open the Interface Designer from the View menu in Alteryx.  This Community article goes into more detail on how to use the Debug tool.   With that, the tree tool is complete.  The Key Values from the selected Tree Interface items are used to select the desired records from the dataset for further processing.   Add a Browse tool to the True output of your Filter to view the results when the app is run. Be sure to configure your app to show the results of the Browse when the app completes. This setting can be found in the Interface Designer, under Properties.   Figure 9   As with many things in Alteryx, there is usually more than one way to accomplish a task.  In the attached App example workflow, there is an additional method to take the results from the Tree tool and query the pet dataset.   All related workflows are attached to the post and saved in Alteryx 10.0 format.
View full article
Sometimes, especially when interacting with tools/files outside Alteryx, you need to explicitly control the order of tool execution. The classic example is a process whereby you 1) INSERT to a SQL table where an auto-incrementing ID is applied 2) read the records back in order to get the auto-incremented id 3) insert the auto-incremented ID into a different table.   In Alteryx worflow streams that terminate (as in an Output or Render), you can't reliably use Block Until Done to control the stream. One way to reliably control the order of events is with batch macros.   Batch macros guarantee that 1) every iteration is complete before the next iteration begins, and 2) that all iterations are complete before the macro reports itself complete. So if you place a process inside a batch macro and create an output stream from that macro, all processing will be done and the macro still can exist in the middle a workflow stream. You don't even need a Block Until Done as part of the process.   Please see the attached examples
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
Calgary  is a list count data retrieval engine designed to perform analyses on large scale databases containing millions of records. One use case for a Calgary database in an  App or Macro  is to offer users certain predetermined criteria (e.g. geography) to select and then return the corresponding data to them. A back-end process can combine multiple data sources, format and pre-filter as needed, and load them into a Calgary database that can be queried quickly by the  app  or  macro . This way, the bulk of the processing happens outside of the app or macro allowing data to be returned more quickly. 
View full article
Sometimes large amounts of data can 'overwhelm' a tool or process in your workflow and make it appear like it is stuck or frozen.  This was the case recently when a user attempted to pass 7 million rows of data to a CASS tool .  Splitting these records into smaller chunks makes the process run much quicker. 
View full article