Alteryx Designer Knowledge Base

  You may have noticed that the Cross Tab tool will insert underscores in your headers.  There may be times when this is ok, but for those times when it’s not, how do you get the original format of the data back - without the underscores?  Attached is an example that provides a solution.  Feel free to bypass the rest of this posting and go directly to the example.       If you'd like a narrative of what is going on in the example, read on.  The Text Input tool has data that has the following data:          We want to cross tab our data, so fields in Dept Name become our new headers with Cost in Millions as our values.         So now our data looks like this:      where the headers have been changed to include underscores. Very uncool.  So in a separate workflow stream, we're going to get the original format.   Attach a Select tool to the Text Input:   And select Program Type and Dept Name.  We don't need the Cost in Millions anymore.  We going to use the Dept Name - which is in the original format - and eventually use this to replace the data with underscores.   After the select, our data looks like this.        However, we only need one group for Dept Name.  You can see it the Dept Name is repeated for each Program Type.  So attach a Unique Tool and check Dept Name in the configuration window.          Now we're left with the following data:         This is where it gets interesting.  We need to make a copy of the Dept Name, but we want to make it look exactly like the headers with the underscores in it.  Why?  We'll use this new field to align our data in sequent Union tool.  In the meantime, back to creating the new field with underscores in the data.       This REGEX_Replace expression will take the current Dept Name field and replace the colons and spaces with underscores.  In the real world, your actual data may contain other characters that were replaced with underscores by the Cross Tab tool.  You'll need to modify this expression accordingly. In this example, the result of the REGEX_Replace statement looks like this:       where Dept Name2 looks like the headers from the Cross Tab tool.    So rather ironically we're going to cross tab this data.  Connect a Cross Tab tool and in the configuration, we'll group by Program Type, make the new headers Dept Name2 and the values will be Dept Name:       The result looks like this:   We're ready to union our data at this point.  Since the headers from both Cross Tab tools look the same, we can select 'Auto Configure by Name'.   Make sure the Cross Tab with the original format is attached to the Union Tool first.         By specifying the output order in the Union Tool configuration, we ensure the original field data is on top (rather than the bottom).     For the grand finale, we'll add a Dynamic Rename tool and configure the tool to 'Take Field Names from First Row of Data'.  Notice that Program Type is not checked in the configuration. That's because this field is already in the format we want, so we unselect that field.      Now we have our data, cross-tabbed, with the original format.  Our headers look the way we want them to!      

The Dynamic Rename Tool is part of the developer category of tools. It allows the user to quickly rename any or all fields within an input stream by employing the use of different methods.   The user has the option to rename only certain fields, all fields, or even dynamic/unknown fields at runtime (e.g. after a Cross Tab Tool). The option for renaming fields are: 

Are you tired of your boring old workflow? Just sitting there in your Designer, slacking off?

In this article, we demonstrate how to import multiple worksheets from one Excel file, how to import multiple worksheets from multiple Excel files, and how to import a specific range of cells from an Excel sheet.    How To: Import multiple worksheets from one Excel file   In the case where you have one Excel workbook with several sheets, you can use a Dynamic Input tool to import the sheets instead of an individual Input tool for each sheet.   Note: for this method to work, the sheets of the Excel file need to have the same schema.                  Use an  Input Tool to select Sheet Names. Connect a Dynamic Input tool to the Input tool. Configure the Dynamic Input tool to read a list of Data Sources from the Sheet Names field provided by the Input tool. The workflow is now configured to import the Excel sheets in a single input stream (you can see the Regions South and West from the two different Excel sheets).    How To: Import multiple worksheets from multiple Excel files   When you need to import multiple sheets from different excel files, you can modify the above method to work by turning it into a Batch Macro.   Start with the same workflow from the previous example. Add a Control Parameter tool  and a Macro Output tool to the canvas. Connect the Control Parameter tool to the top of the Dynamic Output and Input tools. You should see two Action tools being automatically added to the canvas between the interface tools and the standard tools. Connect the Macro Output tool to the output stream of the Dynamic Input tool. Your canvas should now look like this: Now, we need to configure the Action Tools. The action type should be set to "Update Value". We need only to update the file name without changing the sheet name. Therefore, for both Action Tools "Replace a specific string" should be enabled. Please note that this string should contain the path to the file to input without the extension for the sheet.  If the Excel files have different schemas, in the interface designer we can set the macro to Auto Configure by Name or Position so that our workflow does not error out.  Note: sheets within the same file will have to be the same schema. By default, the Interface Designer window will be displayed in the left-hand side of the Designer window.  Click on the Cog icon in the left bar to access the Properties tab. Select the Output fields change based on macro's configuration or data input option.  You now created a Macro. In order to use it, you will need to add it to a workflow. Save the Macro as a Designer Macro (*.yxmc). Do not close the window with the Macro yet. Right-click the new workflow canvas and select Insert > Macro.  Any Macro that is currently open in Designer can be added this way to the workflow. Note that it is also possible to create a Macro repository. This will enable to save Macros in one designated place and easily use them in workflows. See this Help Doc article .  Finally, add a Directory tool, and connect it to the Macro's input. With this configuration, you can import the Excel sheets of multiple XLSX files in the given input directory (you can see Regions North and South from the two Excel files).   Common Issues   The file is being used by another process Unable to open file for read: FILEPATH. The process cannot access the file because it is being used by another process. To resolve, close any other applications (typically Microsoft Excel) that are accessing the file Some of the columns are not imported, but in Excel the file looks fine Alteryx uses the drivers that come with Microsoft Excel to import the files. Sometimes 3rd party software does not write data in the correct Excel format.  To resolve, re-save the file in Microsoft Excel. It should now be correctly imported in Designer.   How To: Input a Specific Excel Range Another functionality of Alteryx Designer is the ability to input a data subset of an Excel file. This comes handy when working with large data.   The procedure to input an Excel range would depend on the Designer version.   Prior to 2018.1 In this example, our data starts in row 5 and column B and ends in row 7 and column D.       Bring the Input tool into your module, then browse to the particular sheet in your Excel file that you wish to pull data from. It will look like the following image.  Notice that Option #3,  Table or Query , points to  ‘Sheet1$’ , we will modify this to point to our data range. To edit the Table or Query , click on the button with three dots ( … ) on the right side of Option #3. Click the SQL Editor button and change the range to SELECT * FROM 'Sheet1$B5:D7'  Click OK. Now click on the Update Sample  link In the Input Tool properties window to see the new range.       2018.1 and later     In versions more recent than 2018.1, the button with three dots (…) on the right side of Option #3 cannot be used anymore. Instead, click into the file path field, and edit it.  Use the following syntax filepath.xlsx|||'SheetName$RangeCell1:RangeCell2'. For example: \sample.xlsx|||'Sheet1$B5:D7' imports the Excel file sample.xlsx from Sheet1, and the range B5 : D7 . Note that the value of 3. Table or Query has changed. Additional Resources   The Dynamic Input Tool Mastery article contains valuable insights on practical use cases of this powerful tool. A more general take on inputting data can be found in this insightful Knowledge Base article. Fun Fact  Bananas grow on plants that are officially considered a herb since the stem does not contain woody tissue.

How to dynamically run the most recent file in a file folder   Sometimes you may have daily, weekly, monthly or yearly data dumps where you want to only run the most recent file. Within Alteryx you can make this process dynamic and seamless through the use of a few tools.   Step 1: Directory Tool The Directory Tool will allow you to browse to a folder and return all the metadata related to the files which exist within that folder. The field of interest in the metadata is the 'Creation Time'.    Step 2: Sort Tool Using the field called ‘Creation Time’ we can use the Sort Tool to sort the date and time values into descending order to get the most recent file at the top of the dataset.   Step 3: Sample Tool After sorting the 'Creation Time' field I now have my most recent file in record 1. Yet, I still have rows of data for the other files within that folder that I need to remove. I can now use the Sample Tool to take the ‘First 1 Record’ and this will result in the latest file information being left.     Step 4: Dynamic Input Currently the field I have in my dataset only shows metadata avaliable for that file such as Full path, Creation Time etc. I now need to read this file and pull in the data by using the Dynamic Input Tool. In the ‘edit’ section select a placeholder file.   Then in the Read a list of data sources ‘Field’ dropdown this will need to be the ‘Full Path’ field coming from the directory Tool. In the Action dropdown this will need to be set to ‘Change Entire File Path’.     Step 5: Run the workflow You can now run the workflow and it will dynamically always pick the latest file from that folder and read the data into Alteryx.   Please find an example workflow attached to this article.

  Sometimes, data will come with multiple header lines, for example, year and quarter might be broken into two rows, like this:      That is not very helpful when you are trying to analyze your data.    You could use a Select Tool and type in new names OR you could do a few transformations and use the Dynamic Rename Tool to merge the rows dynamically, so you are prepared when 2017 rolls around the corner and they start adding new columns to your file!    To fix data that looks like the above, we start with the Sample Tool to split the data into two data streams - one with just the values and one with the header information. Then, we transpose the data to let us fill in the missing years and use the Summarize Tool to concatenate the resulting field values:       Now we can use the header lookup we created as a right input into a Dynamic Rename Tool using the "Take Field Names from Right Input of Rows" option and selecting "Name" as the old field name and Concat Value as the new combined field name.    See the attached workflow for details of tool configurations. 

Sometimes clients have asked how they can re-input the same excel file that they have just outputted into the same workflow. Normally, their gut instinct would be to open up a new workflow and start fresh with the updated file. However, this can be a bit cumbersome especially if they want to do this multiple times or for those wanting to do some sort of logging process in an app. Luckily, there is a quick and easy trick for this.   Attached to this article is a workflow that I go through below...     Now the initial step to this might be different depending whether or not you already have a file that you want to bring in or not. If you are using just a regular Input tool, make sure you obtain the file path by selecting “Full Path” in the “Output File Name as Field” drop down. If you are writing in a text input make sure you have a field specifying the full path. This will be the same file path where we will output the data and bring it back in. The filepaths must be consistent throughout the workflow.     After your data blending and data preparation is complete and you are ready to output the file, place a “Block Until Done” tool at the end of your stream. Make sure your first output is connected to an “Output” tool with your specified settings with the proper file path. Next, place a “Dynamic Input” tool and make sure you are reading the field with the file path and change the action dropdown to “Change Entire File Path”. For the “Input Data Source Template “ box, I just reference the same file that I am going to write to.     And that is how you output a file then re-input it within the same workflow.

Question How do I pass a parameter/constant in a workflow? Answer You can use a workflow constant to pass a parameter.  Click on the Workflow - Configuration - Workflow tab.  Click the + button to add a new User type constant, and give your constant a name and value!       It is quite common to hardcode values (‘constants’) in workflows using any of the following ways:  Text input tool  Variables within formula tool  Hardcode the values within formula/any other transformation tools  If the values are hardcoded within tools, especially in very large or complex workflows, maintenance can be difficult. The ‘constants’ property allows us to define all static variables in one place, so that they are accessible throughout the entire workflow.       

Suppose that your spreadsheet has multiple sheets with the same structure and you would like to read several sheets into your module at once. In this case, the preferred alternative is to use the Dynamic Input tool. 

This error is typically thrown when trying to read a file that does not match the selected File Format, but also in rare cases, from a corrupted file. Error: FileID does not match in the FileHeader   Common causes/ resolutions:   Inputting a yxdb file? Make sure your file type is .yxdb rather than .csv, .kml, etc.   Trying to access a yxdb while it's updating?  If you try to access the yxdb before the module is done running, or if the module crashes/errors during the creation of the yxdb, you'll see this error.  Use a Block Until Done tool immediately before your Output tool.   Using a Directory tool/ Dynamic Input?  Specify the file type.  If your Dynamic Input is pointing to a yxdb template, use the *.yxdb wildcard in your Directory tool, rather than the all-extensions wildcard  *.*   Running an app in the Gallery?  Check 'Replace a specific string' in the Action connected to the Input tool where you specified the file type.   As always, if this does not resolve your error, please open a Support ticket through the Case Portal.   Happy Alteryx-ing!

One of the great features of the  output tool  is the option to take the file or table name (or part of it) from a field. It allows you to append a suffix, prepend a prefix, change the entire file name, or the entire file path. It also gives you the option whether to keep the field on output.

These methods should work in most versions of Alteryx.    One of the most common issues we see from clients trying to read in a .csv file is that they are receiving an error starting “Too many fields in row x”, and because of this Alteryx can’t read in the file.     There are a couple of different ways you can resolve this error.   First, in the Input Data tool, you can tell Alteryx to treat read errors as warnings to allow the file to be read in.     This will change the Error to a Warning so that Alteryx can read in the file so you can investigate.     The other method is to read the file in with no delimiter. You can do this by changing the delimiter in the Input Data tool to \0.     Once you have your data parsed back out into its fields, you can use the Dynamic Rename tool to correct your field names, a select tool to remove the original field, and a simple Trim() function to remove the extra delimiter from your data.   This process is illustrated in the attached workflow, created in version 10.1.    

When you’re frequently writing and rewriting data to Excel spreadsheets that you use for Excel graphs and charts, it can quickly become a hassle to make and remake your reporting objects to keep them up-to-date so you’re visualizing the most recent data. A best practice to keep the hassle out of the process exists, though! If you keep your plots isolated to their own spreadsheet, referencing cell values in another sheet used to capture your data, you can simply overwrite the source data sheet and your plots will update automatically upon launching Excel. In the example below (attached in the v10.6 workflow Dynamically Update Reporting from Excel Spreadsheets.yxzp) we’ve included the workaround to make your Excel outputs seamless.

The Directory Tool gives you a data-stream input that contains information about the files and folders (file name; file date; last modified, etc.) for the location of your choice, which you can then use for more complex interactions with the file system. Basically, the Directory Tool could also finally help me track down my keys - not just where I put the keys in the house, but also how long they've been there, and when they were last moved.

For most tools that already have “dynamic” in the name, it would be redundant to call them one of the most dynamic tools in the Designer. That’s not the case for Dynamic Input. With basic configuration, the Dynamic Input Tool  allows you to specify a template (this can be a file or database table) and input any number of tables that match that template format (shape/schema) by reading in a list of other sources or modifying SQL queries. This is especially useful for periodic data sets, but the use of the tool goes far beyond its basic configuration. To aid in your data blending, we’ve gone ahead and cataloged a handful of uses that make the Dynamic Input Tool so versatile:

If you have a file that doesn't have the field names in the header row but rather in the first row of data, the Dynamic Rename Tool has an option to take field names from first row of data, which allows you to rename your headers easily and dynamically.   But what if you have many files in a directory, all with the same problem?   You want to combine these two files and turn them into one clean output:        But you don't want to have to map each and every one into Alteryx and use a Dynamic Rename Tool for each one of them....   The solution is to wrap a batch macro around the Input Tool and Dynamic Rename Tool. This will let you read in each file individually, fix their header row and then combine them into one dataset!   It's a fairly simple macro:    It accepts full file paths as the input, which are used to update the file mapped into the Input tool inside the macro. The Dynamic Rename Tool is set to accept "Dynamic or Unknown Fields" so it will pick up on any column from an input file. It then replaces the current header with the values found in the first row of data.    The macro "holds" the data until all iterations have run (all files have been read in) and then unions the data together based on field names because we selected "Auto Configue by Name" as the Output Mode:      To use the macro in a workflow, point a Directory Tool to the folder containing the files to be read in and then use the FullPath to feed into the macro.     See the attached workflow for an example.

The key component of any batch macro , the Control Parameter Tool is the gear that keeps things moving. Using the  input , the control parameter accepts a field of values that will be used within the batch macro to reconfigure and rerun the macro for each of the standard input records - unless using the GroupBy feature that matches certain control parameters to buckets of records to be batched through the macro together. Adding this interface tool to any macro will upgrade it to a batch macro and will give you the ability to loop through macro configurations for added customizability. While one of the more sophisticated solutions you can build into your workflows, there are few problems you can’t solve with a batch macro:

In this article we’ll take a look at how to leverage a batch macro to read in multiple sheets from multiple Excel files using Designer version 10.0. The two Excel files I’ll be using in the example are called Fruits and Vegetables with sheets named Apples, Oranges, Broccoli and Spinach, respectively.   The first step will be to read in the list of sheet names from one of the Excel files using an Input tool. This feature is new to 10.0 and is a great addition when used in conjunction with the Dynamic Input tool.   We can then connect our Input tool from the previous step to a Dynamic Input tool. In our configuration, use the same Excel file as our Input Data Source Template. We can use the Sheet Names field from our Input tool as our List of Data Sources and our action is going to be to Change File/Table Name.   Now we’re ready to add our Interface tools to the canvas. Bring in a Control Parameter tool, 2 Action tools and a Macro Output. Connect the Control Parameter to the 2 Action tools, and then connect on to the Input and Dynamic Input. The Macro output will be connected to the output of the Dynamic Input.   In our Action tool configuration our action type will be Update Value. We want to update the File – value that is being passed on to the Input and Dynamic Input. We will, however, only need to update the portion of the string without the sheet name, which is why we’ll also select Replace a specific string: This will allow us to batch our process with multiple files.   Lastly, if our Excel files have different schemas, in the interface designer we can set the macro to Auto Configure by Name or Position so that our workflow does not error out. Note: sheets within the same file will have to be the same schema. We’re now ready to save the macro and put it to use!   Now, on a new canvas, we can place a Directory tool (If all your Excel files are in one directory), or, we can use a Text Input to manually enter each individual file path location. After the Directory or Text Input tool, insert your macro (right click on the canvas->insert->macro), then choose the full file path for your question in the macro configuration. Add a browse, hit run and look at the results!     We now are able to read in multiple Excel sheets from multiple Excel files!   The attached workflow package includes a workflow, batch macro and excel files created in Alteryx v.10.0   Tony Moses Client Service Representative  

One of the best things about Alteryx is the ability to read in multiple files very easily and automatically combine them into a single dataset. This becomes a bit trickier when dealing with files that have different schemas or Excel files with multiple tabs. Adding both multiple excel files with multiple tabs, and having the schema change within each tab takes it to another level.

   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.