Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.
Understanding Join output anchors and when to add a Union tool:  The Join tool anchors are separate subsets of data. You can combine them with a Union tool. The Select functionality of the Join tool applies only to the J anchor, not the R or L anchored data.
View full article
How To: Make Left, Right, and Full outer joins
View full article
A frequent use case we get asked about is how to join two data sets on a date range where one data set has a start date field and an end date field and the other dataset has a just a date field.
View full article
DE30000 / GDC-1220: Snowflake Receive "ERROR: SQLPrepare: SQL compilation error:¶duplicate alias 'Tool1_7c8a' when using Inner Join In-DB Tool with the same Table" when using Inner Join In-DB Tool
View full article
The Join Multiple tool does not work as expected when the ‘Use AMP Engine’ setting is checked.
View full article
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.
View full article
While the Join tool is easily one of the most used tools in Alteryx, it can also be one of the most misunderstood. This is even more likely true if a new user hasn’t previously used joins in any other data-manipulating platform or they are joining big tables where they might not be keeping track of the records inside the fields they are joining on.
View full article
A guide to omit a record if the fields are null
View full article
Often, we get support requests asking if we can do a quick session on best practices for a particular tool or set of tools.  The Spatial Match Tool is certainly no stranger to this request.  Even though Alteryx is extremely fast and efficient with spatial processing, there are instances where a slight change in settings could speed up your total module runtime. For example, let us assume that we have 10,000 records that we are trying to perform a spatial match against.  Upon looking at our data, we notice that the points were derived from the centroid of another polygon, but the previous polygon field was still in the data stream.  Removing the unnecessary spatial field is one of the first steps in optimizing your data stream.  Why?  If you don't need the data, why are you pushing it all the way through your module?  That extra data consumes memory, so removing it from your data stream reduces that consumption, increasing the available resources on your computer.   Ultimately, this practice can be used in any aspect of your data, but it is especially relevant with spatial processing.  For more tips and tricks regarding your spatial match process, download the included zip package.  It contains information that can help you optimize your modules that utilize the Spatial Match tool.   Special thanks to Paul Treece for helping out with the module! Until next time! - Chad  Follow me on Twitter! @AlteryxChad    
View full article
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.
View full article
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.
View full article
Occasionally you may see one of these errors from the Join Multiple tool. It is a result of Cartesian joins.
View full article
How can I create a Cartesian join of my data?
View full article
Scenario: You monitor the mileage of multiple trucks as they deliver shipments over the course of a week and record additional information regarding each truck in a file (Truck Metrics).  Each truck’s cumulative mileage per day is recorded in a separate file (Truck Mileage).  Your goal is to update the mileage-related fields in Truck Metrics with the values recorded in Truck Mileage.  Note: today is Tuesday so only fields for Monday and Tuesday will be updated in in the Truck Metrics file.         Methods:  Manually Select Fields to Update Whether the data is uniquely identified by a Truck Number (or Record ID) or identically ordered in both files, data from Truck Metrics (the table to be updated) and Truck Mileage (the data used for updating) can be Joined together.  Then, using the Select functionality within the Join tool, I can manually choose the fields from Truck Mileage that I want to use to replace the fields that need to be updated (Figure 1).       Figure 1: Fields from Truck Mileage (Yellow) replace fields from Truck Metrics (Blue).  Note that fields in yellow are selected while fields in blue have been deselected.  Fields that need to be included from Truck Metrics (Red) remain selected.    Fantastic!  A simple, straightforward way to update fields!  But, as any analyst knows, working with data is rarely simple or straightforward.  What if you’re dealing with 20 fields that need to be updated, not just 2?  In that case, manually selecting fields to update is not only tedious but also error-prone.  For these types of situations, I recommend a process that allows for a more dynamic approach.       'Verticalize' the Data to Dynamically Update Fields Transposing, or ‘verticalizing’ data, allows for a more dynamic workflow when you have unknowns in your processing.  In a scenario such as this one, you may have an unknown or changing number of fields that will need be updated in Truck Metrics.  Using this approach, we’ll first Transpose both data sets to configure the Field Name and its associated value in a single row (Figure 2).    Figure 2: The data is transposed from Truck Mileage and Truck Metrics.  The highlighted fields in Truck Metics indicates which fields will be updated, as the same field exists in Truck Mileage.     Then, we’ll Join our datasets based on two fields: Truck Number AND Name.  This ensures that fields in Truck Mileage will match to the correct fields in Truck Metrics, assuming the fields names in both tables are named in the same way.  The only selecting we’ll have to do is to make sure all fields from the Right data source (in this case, Truck Metrics) are deselected (Figure 3).  This allows that, in the situation of matched Truck Numbers and field names, the updated values will be used.   Figure 3: The Joined fields indicate the fields that exist in both Truck Mileage and Truck Metrics.  Fields in yellow (Truck Mileage) are selected to reflect updated values in downstream tools.  Fields in blue (Truck Metrics) are deselected.    Note that any unmatched fields have fallen out of the Right side of the Join:     To add them back into the data stream, simply Union the Center and Right Joins together, setting the tool to “Auto Configure by Name”.  Then, to rearrange the data into its original format, use the Cross Tab tool (Figure 4).  And, voila!  Your data is updated!   Figure 4: The updated fields are highlighted in yellow.  All other fields originally included in Truck Metrics are included the in the final results as well.              
View full article
The example below will provide the distance between every store in a dataset to every store in that same dataset. Here's how we did it.
View full article
Performance tuning is an art.  Start sketching today!
View full article
Joining fields with name changes and reordering can be tricky. It's important that the data from Join tool anchors have field names that allow the Union tool to stack the data appropriately.
View full article
One common reason can be due to mixed cases within the data of the join fields between the two datasets. For example, and as shown below, "Orange" from the first dataset is not going to join to "orange" in the second dataset. And this does not just apply to the leading character. Another common reason can be due to the existence of trailing or leading spaces. As also shown in the example below, "Plum " is not going to join with "Plum". The little red triangle in the upper right-hand corner of "Plum" indicates that there are leading and/or trailing spaces.
View full article
Troubleshooting the error: No valid fields were selected.
View full article