Alteryx Designer Desktop Knowledge Base

How do I get from a date like this...MM/DD/YYYY...to where I have the day integer, the month name, and the year in three different columns?

You'll notice some of the menus have been rearranged in version 10+. So if you are looking to export a workflow package, you will find that under the Options menu,  Export Workflow.  By default, you will see all the assets that the workflow depends on to run properly, but sometimes you will want to include additional files to package with the workflow.

With the release of 10.0, people are naturally interested in compatibility with workflows they have developed in 9.5. Additionally, many users will be in a “mixed version” environment as their company transitions to 10.0 and are interested in what options are available to them.   Applications developed in 9.5 can certainly be opened and run in 10.0. We perform a lot of testing and technical work to ensure that Forwards Compatibility is fully functional. Anything that was built in 9.5 should continue to work in 10.0.   Undoubtedly, the recommendation is to perform the 10.0 upgrade across the organization when all users are ready for the change. This eliminates any issues with “backward compatibility” and makes it easier and seamless to share applications - workflows, macros, etc.   If you do find yourself operating in a mixed environment, there are some things to be aware of:   A 9.5 app that is opened in 10.0, then saved in 10.0, will no longer be accessible via 9.5 If you publish apps to a server, you will want to make sure that the server version is compatible with the apps you will be publishing. If you have 9.5 apps then you can leverage both a 9.5 server, as well as a 10.0 server. However, if you’re developing in 10.0 then the server will also need to be 10.0 If you need to support both environments for a period of time, it is possible to install two versions of Alteryx on a single machine. Admin and Non-Admin versions can be installed side-by-side. (e.g. If a user has the Admin version of 9.5, they can install the Non-Admin version of 10.0 and switch between them.) Recognize that if you publish apps to the server, you will need to be running the Admin version of the same version as what is running on the server.

We've had a few questions on importing fixed width files within Alteryx. Here is a quick 'how to' guide in reading in un-defined file formats!

How to create your own custom tool palettes

 Alteryx has a full set of integrated predictive tools but even with developers working at full speed, it is hard to keep up with the R community. Sometimes users want to install and utilize their favorite R packages. This post demonstrates how to use and install additional R packages.

The Email tool is designed to send an email for each record that you input -- if you attach an Email tool directly to your output data stream, it will generate and send one email per record – e.g.: if you have one thousand records in a report you'd like to send, the Email tool will send one thousand emails.   The reason for this is that you may have a list of email addresses as recipients, or you might want to use a separate subject line for each department in your organization; that is what the input side is for – to allow you to dynamically populate fields such as the "To" and "From" fields, or even the body of the email itself.     If you're not populating your Email tool from your data stream, to keep the Email tool from sending a thousand emails, first separate a single record from your data stream, attach the email tool to that single record, and then hard code your configuration – including attachment -- into the Email tool. There are a lot of ways to accomplish this - a Sample tool, or a Select Records tool, or a Unique tool will all get the job done. The Email tool will execute at the end of the module, regardless of what is attached to it. Based on this, once you've written out your results using an Output tool, another option is to simply attach an Email tool to a Text Input anywhere in your workflow and hard code your configuration into the Email tool with the attachment specified – just don't put more than one record in your text input!    

Compariing spatial objects for equality is very subtle, because you have to define what is meant by equal.  In the case of polygons, you can have 2 different polygons that describe the same shape, but have a different start and end point.  Most people would say these are the same, but if you compare them exactly, they are in fact different.  For instance:   [ -104, 39], [ -105, 39 ], [ -104, 38 ], [ -104, 39 ] [ -105, 39 ], [ -104, 38 ], [ -104, 39], [ -105, 39 ]   These 2 sets of points describe the same shape just from different starting points.  The same problem exists with lines - the same line specified backwards and forwards looks the same on a map, but would not be superficially equals.   The easiest way to test for equality is to test: does object A contain object B and also, does object B contain object A.  If they both contain each other, they must cover the exact same area on a map.   In a formula or filter tool, you can say:  ST_Contains([A],[B]) AND ST_Contains([B],[A)   In a SpatialMatch tool, you have to use a custom DE-9IM string to describe the match you want.  In this case, use the string "T*F**FFF*".  I won't try to document that string - see https://en.wikipedia.org/wiki/DE-9IM for a reference of how that works.   Attached is a module (for Alteryx 10) that demonstrates both techniques:

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  

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

Those of you who have used the Report Map tool to create thematic maps have likely been unimpressed with the way Alteryx outputs the thematic legend text. Alteryx added two little known/used tools: the Map Legend Splitter and Map Legend Builder. With a little finesse, you can get the legend to go from completely unformatted to fully customized.   Before After       Not only does this allow for an easier to read the legend, but it also can save valuable space on your map or document. The example above simply involves taking the default thematic output legend text and replacing it with user-defined text for those layers.   Here's How You Do It    The entire workflow is illustrated after all of the steps below. In the Report Map tool on the Legend tab, change Position to "Separate Field". This will output the map and legend as separate objects, allowing you to work with just the legend. Add two Select tools after the map. In the first Select tool, select only the legend. In the second, only the map (and BoundingRect, if needed). Add the Map Legend Splitter tool after the Select tool that selects the legend, and select "Legend". Add a Record ID tool which will be used later to re-sort the legend back to its original order. Add a Filter tool using the [ThemeName] field in order to extract just the records which make up the thematic part of the legend. For this example: [ThemeName] = “Block Groups”. Create a Lookup table containing the Record IDs and the new text for the legend rows that you want to replace. Join the lookup table to the legend stream using RecordID. Deselect the original “Text” field and rename the “NewText” field to “Text”. Deselect the second RecordID. Union the new modified legend rows back with the non-modified legend rows using “Auto Config by Name”. Sort the records back to their original position. Use the Map Legend Builder to rebuild the new legend. The default configuration is all that is necessary. From this point, you can choose to either overlay the legend on the map (using the Overlay tool), or join the legend back to the map (using “Join by Record Position” in the Join tool) and position the legend adjacent to the map as desired using the Layout tool.   Below is the entire workflow numbered by the steps above. Attached is a sample workflow created in 10.0.  

  Often in spatial analytics, you’ll need to find the closest spatial object to another. The most intuitive way to do that is through the Find Nearest Tool, which specifically captures the ability to find the shortest distance between spatial objects in one file (targets) and a user-specified number of objects in another file (universe objects). This tool does an amazing job of simplifying the process of finding the nearest object to another but it can also add significant time to your workflow.   I often elect for an alternative method that has trimmed significant run time off of many of my spatial workflows. That is, using the Append Fields Tool to duplicate your target spatial objects for each universe and using the Distance Tool to calculate DriveTime. After that’s done, simply add on a Summarize Tool, group by the target and take the “Min” DriveTime for each. You could also sort ascending by DriveTime and sample for the first target by grouping with that field. There is a caveat, however, as the Append Fields Tool drastically increases the number of records in your input and will only speed up the process if there are significantly more targets than universes.   These methods are distinct in that the Find Nearest Tool must do a DriveTime run from each target spatial object to each universe spatial object (200 DriveTime passes in Example 1) whereas the Distance Tool approach already has all the points available to it and recognizes that there are many more targets than universes. As a result, it runs the reverse-direction DriveTime calculation starting from each universe to all target spatial objects at once (5 DriveTime passes in Example 1). If it is quicker for you to use the Find Nearest Tool, be sure to shed the spatial objects you no longer need in your workflow as soon as possible, even inside the Find Nearest Tool’s configuration if possible. That could also reduce your run time due to the sheer size of the spatial object datatype. Below are some examples of the methods. They can also be seen in the attached workflow, AppendAlternative.yxzp.   Example 1 Targets: 200 Universe Objects: 5   Attempt 1: Find Nearest Tool     Run Time: 8 minutes 13 seconds   Attempt 2: Append Fields Tool and Summarize     Run Time: 11.9 seconds   Example 2 Targets: 100 Universe Objects: 52   Attempt 1: Find Nearest Tool     Run Time: 49.7 seconds   Attempt 2: Append Fields Tool and Summarize     Run Time: 12.6 seconds

  We recently had a user that was looking to distinguish polygons between each other using dashed lines, a style not currently available in the Report Map Tool. But that’s alright, we can use the opportunity to showcase how you can be creative in Alteryx by using a few tools.       In cifically filter out the record IDs you want to change or use the Sample tool to pull random records, or 1 of every N Records. (FYI – if you use the record ID, you will want to remove that column after you have split the records, due to the record ID used later in the mapping process.)   Once you have selected the polygons you want to use, you will need to break those polygons into individual points using the Poly-Split tool. Here, choose Polygon field and Split to Points. Splitting the polygon into points will allow you to adjust the polygon by each point.   Then, you'll want to remove some of the points to create the “dotted line” effect by using the Sample tool. This tool's settings will want 1 of every N Record selected (you can change the N=3 to any number you like that will have the spacing effect you want).   Now that you have removed some points, you'll want to do a few things to give the points a grouping effect. In order to do this, add another Record ID tool, then filter the record ID by odd and even numbers. You can do this using the Filter tool and using the expression mod([Record ID],2)>0. Then add Record ID tools to the T and F anchors to complete the grouping effect when you add them both to a Union tool.   Grouping the points allows you to build your Polylines. After the Union tool, add a Poly-Build tool. The Build method will be Sequence Polyline using the SpatialObj and the RecordID, as the Source and Group fields, respectively.   Your final step is to add a Map tool and pull in the data from your Poly-Build tool, as well as the original centroid points of the polygons with which you created the split lines, and finally the remaining polygons you want to be represented as full lines around the radius. When configuring the Map tool, remember that the points coming from the Poly-Build are actually lines and not Polygons. Your layering will need to have Points, Lines, and Polygons to complete the map.

When importing a delimited file, have you ever run across an error saying ‘Too many fields in record…’ and it’s something like record number 1478254 and you think to yourself, “how the heck am I going to find that record and correct it?”   Well, let us help you with that.   This message is essentially telling us that some particular row of data, when parsed, has more fields that the first row of data (usually caused by an extra delimiter in that row). The first step in identifying the row is to change the delimiter in the Input Data configuration window. Change the delimiter from the current delimiter to \0 (that is a backslash zero, which stands for no delimiter). This will read in your file but instead of parsing, it’ll just read in the data as is.   Current delimiter:   Versus:   No delimeter:   Now just add a RecordID tool and Filter tool so we can filter on RecordID 2 (or 1478254) to find the row that is holding us up.     Now that you’ve identified the row that is causing issues, you could go back to your original data, correct it and try importing it again.    If you were looking closely at the example data, you may have noticed that record 4 also had a different number of fields as row 1.  After correcting row 2 and importing , again, we would get an error for row 4. Just follow the same procedure to correct additional errors.  This could probably be done through an automated/iterative process, but that will have to wait for another time.   (attached is an example workflow created in Alteryx 10.0)  

Sometimes you only need to read in specific fields from a database or input. Within Alteryx it is as easy as using a Formula tool, Dynamic Rename and Dynamic input!!   The first step is to bring in the master file and the list of field names you wish to carry through from the Master file.   Next attach a formula tool to the field names list and create a new field with the expression "Keep-" + [FieldNames].     This will rename the fields with “Keep-” appended to the field name.   The next step is to use a dynamic rename tool to “Take Field Names from Right Input Rows”. In the 'New Field Name from Column" select the field which has just been created above.   Use a dynamic select tool to ‘Select via a formula’ and use the expression below to find the field which has ‘Keep-“ appended to the left hand side.  This will then only bring through the specific fields from the Master file which you have listed in the field name input.    Using a dynamic rename you can return the fields to their original names through a substring function. This will remove the "Keep-" from the field name.       You now just have the fields you want from the Master input file!      Jordan Barker Client Service Representative

My code runs in R, but not in the R Tool?

  One of the great things about Alteryx is the ability to have multiple geography types (points, lines, regions) all contained within the same record.     However, when exporting map layers to MapInfo Tab file format, a decision has to be made of which spatial field to keep.  What if you want all of them?!     Not to worry! The Transpose tool will save the day!     To configure the Transpose tool all fields will need to be selected under the Key Fields except for the spatial fields, they will be selected under the Data Fields.       Just note that there are now 2 fields for the radius. RadiusSize will correspond to the first trade area (row 2) and RadiusSize2 will correspond to the second trade area (row 3).   Now, more importantly all the spatial objects exist in one field (the Value field) and can now easily be exported to MapInfo.      Note: this methodology will also work for ESRI shapefiles, however, all the spatial objects must be of the same type. All points, all polygons...   (An example workflow is attached. This workflow was created in Alteryx 10.0)

Alteryx Analytic Applications (Apps) let us take a process, parameterize parts of it, and add an interface so that end users don't need to know all of the inner workings of the process in order to make it work for their specific scenario.

Symptoms   My workflow was doing just great, reading in a bunch of stuff and cranking out new data like a boss. But then I saved the workflow, closed down Alteryx, and now when I try to open that file up again, I get the following error:   There was an error opening "C:\my file name.yxmd" There is an unclosed literal string. Line 3042, position 42.   Does this mean anything to you guys? Diagnosis The workflow has likely become corrupted, perhaps after saving it across a network, and has dropped something in the save process.  Solution   Alteryx creates backups of the workflows (same name with a .bak extension). Try to open the .bak file in Alteryx.    Go to File ->Open and change the "Files of Type" to All Files. The .bak files will show up and you can open the one you are having difficulty with.    Another alternative would be to check if Alteryx auto-saved it prior to your last save. Go to File -> Open Autosaved files.

For 10.1, we updated our installer screens to something a bit more modern looking. Here's a comparison:         Some users may run into an issue where the new Alteryx installer screens do not display text correctly if Alteryx is being installed on a machine using a Windows Classic/Windows Standard theme. (See the image to the right.)   Windows 2008 R2 Server OS system uses this theme, so users with this OS may encounter the issue.   We believe this problem will only occur if the machine also has a previous version of Alteryx installed.   If the issue occurs, there is a simple work-around: Quit the installation Uninstall the previous version of Alteryx using the Control Panel. After the uninstall is complete, run the installer again. The installer screens should display properly.     This information is also available on our Release Notes page (http://downloads.alteryx.com/). It's always a good idea to read through the Release Notes when downloading a new version. Key Features - highlights exciting new enhancements added for the release. Additional Changes - lists minor updates as well as notable bug fixes. Known Issues - documents small issues (like the one outlined above) and work-arounds to use if you encounter them.   Although 10.1 is a minor release, it contains a number of exciting new enhancements, and we're looking forward to hearing what you think about them.

Regression analysis is widely used for prediction and forecasting. Alteryx customers use these statistical tools to understand risk, fraud, customer retention and pricing, among many other business needs.