Alteryx Designer Desktop Knowledge Base

Convert Polygons to Polylines This macro can be used to convert Polygons into Polylines. In order to use the macro you must specify a spatial field (polygon) as an input. After running the module all the polygons in the selected spatial field will be converted to polylines. This macro is very useful for when you are putting polygons on an ALTERYX base map and want their boundaries to appear on top of the base map roads. An example would be the mapping of census based Trade Areas. As census Geographies tend to follow roads, they are often obscured by the Alteryx base map. Before Example After Example  

  Macros Save Time    One of the best time savings tactics for a developer is to create their own macros for repetitive processes.  In the case of this macro, an Alteryx user may need to know what week of the year an event falls on.  For example, how many transactions occurred during the 23rd week of the year? Accomplishing this task requires formula tools, time/date fields, and come clever parsing to account for leap years.   The first step is to separate the date into its component parts.  By separating the date into its component parts, a calculation can be done later in a formula tool that will create a date for January 1st of the year of that specific record.           The next step is to use a Formula tool to divide the [Month] and [Day] fields by themselves, to create a 1/1 date.  This date will be compared against the record date to determine how many weeks it has been since the start of the year.                 The final step requires several formulas in one Formula Tool to complete the process. Doing math using the Date formula calculations in a Formula tool requires a specific formatting.  The first two formulas determine whether the month is a two digit month or a single digit month. The following two formulas detect whether or not a leading 0 is necessary for proper date formatting and inserts it. The fifth formula brings the components together to create the properly formatted yyyy-01-01 date to compare the record date to.  The sixth and final formula determines how many days have passed from January 1st to the record date, divides by 7, and rounds up.  The result is the week of the year that the record fell on.                 There we have it!  Please find the formulas and configurations in the attached workflow.    

Many skunk works type of products are never really seen by the public eye (such as the Boeing Joint Strike Fighter prototype).  The beauty of being a part of a software company that has both desktop and web products is that we have the ability to play with and show off our prototypes to the general public.  Enter interactive visualizations within Alteryx!   (NOTE: The methods described in this article and the linked blog posts serve the sole purpose to display the flexibility of Alteryx and there is no current or future plan to further develop or productize this functionality.)   The image you see above was directly pulled from an Alteryx output.  You may or may not have read the blog of our CTO, Ned Harding (If you haven't, I highly recommend subscribing!).  His recent posts revolve around a topic that has been brought up quite often in the Alteryx circles, reporting and visualizations within Alteryx.   In these articles, Ned displays a method to incorporate HTML5 and Javascript into a few macros with incredible results.  The first is a more static but visually pleasing output, while the second is a Public Gallery app that displays the use of interactive output.  Both are very interesting displays of the two technologies working in tandem to help the user better understand their data. Check it out, test it out, and visit the links to the Public Gallery within the blog.   Part 1: Alteryx: JSON Data Output   Part 2: Alteryx: HTML5 Visualizations   Part 3: Alteryx: Interactive HTML Visualizations

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

Tired of pulling conversion rates manually? Me too!   Let’s build ourselves a currency converter in Alteryx Designer version 10.1 using the http://fixer.io/ exchange rates. Fixer.io is a free JSON API for current foreign exchange rates published by the European Central Bank. The rates are updated daily around 3PM CET.   The first thing we will do is gather the API documentation from the Fixer.io website. This will give us the URL we need to make our REST API call. If you are unfamiliar with making REST calls please take a look at Tasha’s

Some users have reported a problem when importing and exporting macros within the Alteryx Designer when the Regional Settings for the machine are not set to English (United Kingdom) or English (United States) (see screenshots below).

Is it possible to have a question included in a macro that would not show to the user in the configuration window?

It's not uncommon to have a situation where you need to conditionally join one dataset to another.  Perhaps the most common is when you want to join one file to another where a date from the first file is between, greater than or less than a date(s) on a second file.  The default tools found in the Join section of the tool palette don't provide a simple way of doing this (it can be done, but you need to string several tools together to make it work.  There is a better way!  Read on...).    There is great macro available in the public Alteryx gallery called Advanced Join (find it here, but spoiler alert... you can download the attached workflow which includes this macro so you don't have to go to the gallery to get it).  The Advanced Join gives you greater latitude than the Join tool.  Most notably, you can select records from file A that are unique to A AND intersect with file B.  Now you may be thinking, “I can do that by unioning the records from an inner join with records from a left join,” and you would be correct.  But it takes two tools to do what one Advance Join does.  More importantly, the Advanced Join allows you to put a conditional statement on your join which is something you can't do with the Join tool.  And it’s this feature - the ability to use conditional statements in a join - which we will focus on for our purpose here.   Let's get into some examples.  I have a file, 'Fruit List’, which contains data about various fruits.  This file contains a Column Id, a Fruit Name, a Start DateTime and an End DateTime:     I have a second file, 'Greek Alphabet’, which contains a Column Id, a Greek letter and a Datetime.       I want to join the two files on ColumnId where the Datetime from Greek Alphabet (file B) is BETWEEN Start Datetime and End Datetime from Fruit List (file A).  Here's the workflow and a screenshot of how to configure the Advanced Join:         And here are what my results look like:       Only one record from Greek Alphabet matched one from Fruit List on ColumnId where Greek Alphabet's Datetime was between Fruit List's Start Datetime and End Datetime.   In the next example, I have the same Fruit List file and want to join it another file, Greek Alphabet that contains just one datetime filed:       The first thing to note is both files have a field called 'DateTime.'  We'll want to give these unique names to avoid ambiguity when we write our conditional state in the Advance Join configuration.              I want to join both files on ColumnId but only when DateTime from Fruit List is LESS THAN DateTime from Greek Alphabet:         And the results...:       Let's look at one last example.  This time, I'm going to use the Fruit List and Greek Alphabet files used in the first example (Fruit List has a Start DateTime and an End DateTime).  I'm interested in matching records where DateTime from Greek Alphabet is BETWEEN Start Datetime and End DateTime from Fruit List.  I'm not matching on ColumnId this time.     For the Advanced Join configuration, I'm going to cross join my files.  (CAUTION: the resulting join could contain as many rows as the product of the number of rows of the incoming datasets - a.k.a. Cartesian join - depending on how restrictive your conditional is.  This means if you're joining 2 datasets that contain a million records each, the resulting dataset could contain as many as one trillion records! ).  If I had wanted to match on ColumnId, I would have had to do that separately using a Join tool. The cross join option only allows you to apply a conditional statement:       Results from our 3rd example:     Notice how 10 records from Greek Alphabet were joined to just one record from Fruit List.     The Advanced Join tool can save you time and a lot of headaches when you want to join files using a conditional statement.  It has some limitations - you can only join two datasets and include one conditional statement per tool, cross join limitation mentioned above - but Advanced Join provides greater capability and flexibility than the standard Join tool.

This article covers how to delete records from a database using an Input Text tool containing a parameter and a macro using the Post SQL Statement. The macro below is an example of how you can do this.

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.

The Detour Tool allows alternative workflow paths to be run according to its design-time configuration.

You may have a use case whereby you have a large dataset and you want to output it to separate excel files. However, in each of these excel files you would like to apply a template format.

This article is part of the CS Macro Development Series. The goal of this series is to communicate tips, tricks, and the thought process that goes into developing good, dynamic macros.

Did you know that instead of having to choose a save location in the Output Tool, you could leverage the directory that is used to save your temporary files?

This article is part of the CS Macro Development Series. The goal of this series is to communicate tips, tricks, and the thought process that goes into developing good, dynamic macros.

Configuring batch macro output fields horizontally

As currently designed, the Amazon S3 Download tool only allows one file, or object, to be read in at a time. This article explains how to create a workflow and batch macro that will read in the list of objects in a bucket and allow you to filter for the file(s) you want using wildcards!

Using Iterative Macro to generate a list of random numbers

Configure a Workflow to use a Tree Interface Tool

Improving processing speed up to drive time trade areas