Alteryx Designer Desktop Knowledge Base

When creating reports within Alteryx, you can name Excel tabs by specifying a column to use in the Layout tool. This is done in a 3 step process. Step One:   Choose your grouping column; below, the Table tool is being used and [DMA_Name] is being used for the Group By field. This would also apply with the Charting and Map tools.         Step Two:   In the Layout tool, change the Layout Mode to Each Group Of Records. Next, choose the column you would like to Group By; for the example we will use [DMA_Name]. Next, change the Orientation to Vertical with Section Breaks. By doing this, the Section Name option will appear at the bottom of the tool configuration and allow you to choose the column you would like to use to name the Excel tabs.       Step Three:   Update the Render tool to create an Excel spreadsheet. In our example, each of the Excel tabs was named for the DMAs contained in the data.           Keep on reporting!

The Report Map tool allows the user to define theme settings/ranges and to modify the size, icon, and color of the display for each range, and this can be done rather easily. First, in the Map tool on the Data tab, pick which column you would like to theme off in the Thematic Field selection area:                            Once this column is selected, go to the Layers tab in the Map tool and expand the layer options for your theme layer. Click on the Theme and options will appear on the right. For the purpose of defining your own theme settings, you will want the Tile Method to be on Unique Value, which gives the Specific Values area.                             The Specific Values area is where you list what you want to theme on. For this example, we are theming off the DMA_Name so you would enter each of the DMA names you would like to theme. If you have a lot of ranges, you could also use a Summarzie tool in your module and Group By your theme column, thus giving you a list of your theme values. Run the module once to populate the Browse tool and you can then click and hold on the first row and drag down to the row of your choosing, selecting them all. Ctrl+C will copy the rows and you can paste them into the Specific Values area using Ctrl+V                             Once the values are entered, click on Refresh and a layer option will show up for each of the theme values you set.                           Now that the theme values are layers, you can go to the Style option under each layer and change the Point Style, bring in a Custom Point, change the Size, Color, modify the Outline Color and Outline Size.                           If you don't define all the values that are contained in the data you are bringing thru, the Map tool also provides options on what to do with these.                           This can also be done with number ranges with a few small changes. For Tile Method, choose Manual Tile. Enter in the cutoff for each range that you would like to be able to theme.                          Hit Refresh and the new layers for the theme ranges will be displayed, allowing you to modify each one. Also note that layers are created for the ranges below and above what you specify in the Cutoff Values area.                         

Copy:  In a Browse tool, under the map tab, right click on any point on the map.  The Latitude and Longitude for that location will be displayed.  If you click on “Copy Point …” you will have copied that Latitude Longitude value to your clipboard. Paste:  Right click on the module canvas, select “Paste” and a Text Input tool will be added to your module, with the values of your copied point already populated in the tool.

Tips and Tricks for Fuzzy Matching.

The Auto Field tool examines your data, and automatically optimizes the field type and length.  Take a look at your data with a Select tool, follow this up with an Auto Field, and follow that up with another Select tool to see what kind of changes you’ve made.  After you run the module, you can examine each Select tool to garner a before-and-after view of the adjustments made to the fields.  You can even take this a step further, and add a few Browse tools to see how your database actually decreases in size, you may be surprised by how much! In the below view, the file size was reduced by about 40% with the Auto Field tool, on just 50,000 records and one field. Now consider running a file of millions of records and the amount of the size decrease becomes really substantial! Before Auto Field View: After Auto Field View:

You can control-click any number of tools to highlight them, then move them around as a group. You can click, hold and drag (to draw a box), and all tools covered by the box will be highlighted as you go; release, and then pick up and move that assembly of tools around as a group. Employing the tactics in a and b above, you can also copy and paste tools within your module.  You can cut too.  To go a step farther, you can copy tools in one module and paste them into another, which is a great way of breaking a process out for a little more focus, or to minimize the amount of processing your machine does every time you click Run. Another great tip is to Copy and Paste your Text Comment tools.  Perhaps you’ve taken our advice about annotating your modules…  You can create a module that’s designed only as a container for your most frequently used Text Comment settings.  This will save you the effort to change background colors, font sizes, etc. every time you add a Text Comment. Depending on the tool, formulas and other configuration selections will carry over when copy/pasted.  This is especially useful when configuring formulas that have been used in previous modules.

Sometime it is difficult to locate a specific tool within a module.  It becomes especially difficult when your module grows very large, or when you’re reviewing a module built by someone else. The solution: Use the Find Tool feature. In any module, you can go to Edit > Find Tool... (as seen above) or simply right click on an empty portion of the module canvas.  Click on “Find Tool…”  This will open a new dialogue box with all the tools in the module listed by tool number.  Clicking the “Sort by Name” Link will re-order the tools alphabetically.  When a tool is chosen in the dialogue box, the tool is automatically selected and the properties window for that tool is opened.  If the tool name is known, it can be searched in the “Find Tool” box. This is a great feature in Alteryx that we made even better for 6.0.  In addition to searching for a tool name or user-specified name, you can also search tools for their content, or, like in the case of a Formula tool, parts of an expression.

The Tool Container is a tool that allows you to organize your module by placing tools inside of the Tool Container.  Because the Tool Container can be opened and closed without impacting the behavior of the tools they contain, you can effectively minimize sections of your module so as to bring greater clarity to the overall picture.  From a visual perspective, the Tool Container encourages object-oriented design! Another feature of the Tool Container is the ability to disable/enable an individual Container.  An example application for this is a module where you need to do some design/testing, but you do not want to write the Output file until everything is working properly.  By placing your Output tools inside of a Tool Container, and then disabling the container, you will be assured that no files will be generated.  Re-enabling the Output tools is a piece of cake; just uncheck the “Disabled” checkbox. This feature allows you to easily control the generation of Output files or the processing of portions of your module.  For users developing complex modules, the Tool Container delivers a powerful time-saving tool for the module design process.  

If you have a file that you want to output to separate Excel files you can first create the desired file path with the Formula tool and then utilize the Output tool to change the entire path.

If you have two or more files, different structure, and you would like to output each file into a separate tabs in an Excel spreadsheet. You could use the table tool to create snippets and the Layout tool to create sections breaks. Bring in your files using the Input tool and connect them to Table tools to create the snippets.  Finally, Join them by record position. The Layout Tool properties should look as follows.  Select Vertical with Section Breaks for the Orientation setting. The output will show each file in a separate tab:

How do I output to an Excel template file? It is possible to output your data to an existing Excel document that already has modified formats and column names. For example, the below Excel file has existing data in the first 4 rows. If you wanted to add addresses to this file while keeping the first 4 rows, the first step would be to highlight the area you want to write to. If you don’t know the exact length/width of your data, I would recommend going large: Once you have your desired area highlighted, right-click and choose the Define Name… option: A popup box will appear, enter in a name of your choosing and click OK: You also need to make sure that the sheet you are saving to doesn’t contain any spaces in the sheet name. Once verified, save the template and close out: Below is an example of the sample data that will be added to the above template: In Alteryx, use a Input tool to point to the data you would like to use to update the template file: In the Output, you will want to choose the template file, which will cause the below message to appear, choose yes to overwrite: When saving to Excel, the below window will popup, enter the name you used for the range you highlighted in the template file: After clicking OK, the Output configuration area will populate. Change the Output Options to Delete Data & Append: You can now run the module. Once the module is finished, you can open the updated template file, you should see your previously formatted rows/columns plus the new data you wanted to append: If you set a format to the range you named (color, text style, bold, etc), Excel will keep it so that the data you are writing to the file will appear with the specified format.

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. 

Tips and tricks on how to output multiple sheets to an Excel file with the Output tool or with Reporting tools. 

Buffer – This tool was designed to work primarily with Lines and Polygons, but is effective with Points as well Trade Area – Only works with Points.  If you create a trade area around a polygon, the Trade Area tool will use the polygon’s centroid as the basis for creating the trade area.  The Trade Area tool should only be used with point spatial objects. Non-Overlapping – Points Only.  To create non-overlapping buffers around Point spatial objects, use the Trade Area tool instead of the Buffer tool, and check the “Eliminate Overlap” checkbox.  It is not possible to create a non-overlapping trade area from line or polygon spatial objects.    

The purpose of this process is to demonstrate the use of the bounding rectangle as a means of processing spatial data. 

In its spatial tools, Alteryx includes an option to create a convex hull polygon from a series of points. However, depending on the type of analysis requested, it can be more ideal to create a concave hull instead. An example of this would be the need to group customer points into trade areas thematically based on store location. If the standard method of convex hull polygon creation were used, it would be possible to create polygons that overlap, which would not be the desired outcome. A demostration of this is shown below. Module attached. Example: To avoid this, one can first project non-overlapping trade areas from the points and spatially combine them to eliminate the overlap. To then remove the excess area projected, the object can be trimmed to the original convex hull boundaries. To further process and remove the resulting holes, spatial generalization can then be used.

The addition of a reference map can bring your report to another level of utility.  This is a technique that is often used when a module is developed to produce a multitude of maps; each zoomed-in upon a small area.  For users who may be unfamiliar with the area displayed in the primary map view, a reference map provides a rapid means of orientation. The key to creating a reference map is making use of the bounding rectangle sourced from your primary map.  The bounding rectangle will be used as a display feature (layer) in the reference map.

Alteryx provides GUI tools that offer similar functionality to many SQL commands. Although minimal SQL scripting may be necessary in order to properly configure tools, the amount required to complete analysis is significantly reduced.

How To: Combine Date Ranges with a Macro This module (and embedded batch macro) will provide a comprehensive timeline or date range(s) using multiple, overlapping date ranges.      The macro converts date ranges into spatial objects in order to use the spatial functions in Alteryx to group overlapping or adjacent ranges. This ensures that ranges A and C get are grouped together when A and C do not overlap but both A and C overlap range B (and so forth for larger chains of ranges).           It also allows for “jumping” a user-determined number of days in order to combine regions that do not overlap but are within a specified number of days of one another.      

Overview: I wrote this as a short example into how one might use Alteryx to write a further Alteryx module to do complicated or repetitive tasks dynamically that would be difficult to do through the front end. This module will automatically produce another Alteryx module that will do frequency statistics for a file. This should save the manual time (for files with lots of columns) adding a summarize for each column. It also saves transposing the file (which for large files is very slow to run). Instructions: Open Producer.yxmd Change the input to that module to whichever file you like (or use Testing.yxmd which is provided) Run it – this will create the Result.yxmd module Open Result.yxmd – and change the input in the module to be the same file you used in step 2 Change the output if necessary (it defaults to an Alteryx database) Run Results.yxmd At the moment it does deal with &’s and single quotes in files, but won’t do anything clever like do stats on substrings for long fields.   I hope this inspires people to use this technique and build on the module I’ve built.  

Business Problem: Thematic maps are often used to display data geographically with colored or shaded themes, but sometimes users wish to see the data differently. For this purpose, dot density mapping has become a frequently requested feature for map rendering in Alteryx. Dot density creation is possible with the inclusion of the spatial function within the formula tool. This function, ST_Random Point, will randomly disperse a point within a given polygon. Utilizing this tool, anyone can create a macro to produce the data required to generate a dot density map. Actionable Results: Easily create dot density thematic maps Overview: It can often be convenient to view thematic maps as clustered points. This type of visual output is a logical and accurate representation of data occurring in a non-continuous distribution. Vertical: Any Key Tools Used: Formula Tool (ST_Random Point spatial function), Generate Rows Required Input: As inputs, the Dot Density macro requires two fields: geography with an associated value and a configuration of the number of dots per value. Determining the appropriate number of dots per value may require some trial and error to produce desirable results. Knowing the min, max, and median values associated with the base geographies would help you to determine and optimal dots per value. This coupled with the size of dots on the map will greatly affect the aesthetic of the mapping.