Alteryx Designer Desktop Ideas

Joins are a vital part to any analysis.  Relating data together is the backbone of bringing data together.  Currently the Join Tool allows the relating of one or many data fields that are assigned to be equal to each other (aka Equi-Join).

As creativity evolves and users aspire to construct more complex relationships, Non-Equi relationships become more prevalent.  What are Non-Equi relationships?  Simply put, they are Non-Equal.  Examples:

• Field_A  <  Field_B
• Field_C  >  Field_D
• Field_E  <=  Field_F
• Field_G  >=  Field_H
• Field_I  !=  Field_J

Non-Equi relationships are especially useful when working with dates that fall within a range of dates contained within two other fields.

• Example:  Target_Date BETWEEN Low_Date AND High_Date

Currently, to accomplish this, there are a couple options:

1. Generate Rows:

• You can use the Generate Rows Tool and fill in the range of values
• Then use the traditional Equi-Join matching the generated date to the Target_Date
• And persist only the INNER join stream
  
  Lots of tools to accomplish a "simple" task

<or>

2.  Cartesian Join:

• You can use the Append Tool to replicate every Target_Date onto every row containing the Low_Date and High_Date fields
• Filter where the appended Target_Date is between the Low_Date and High_Date
  
  Memory intensive, creating many unnecessary data rows, and may ultimately not work with large datasets

A simple solution, or alternative, would be to enhance the existing Join Tool to allow for choice in the "Join by Specific Fields" configuration section.  For example:

Adding in a drop-down menu per field pairing, the additional Non-Equi options could be added.  Equal would be the default, but users could otherwise pick the relationship type to accomplish the same "between" condition.

Here's a zoomed image of the look and feel:

The benefit is a much simpler configuration within the workflow, avoiding extra tools and creating a bunch of extra data rows that aren't relevant to the result.

If you're reading this and would like to see this enhancement to the JOIN Tool, consider a quick click on the like button.  It helps ideas like this get more exposure and lets Alteryx know this is important to you!

Cheers and thanks for taking the time to consider this idea!  -Jay (jrlindem)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Acknowledgements:

It's important for me to point out that this isn't the first time this idea has been posted.  There are others that share the sentiment for both Non-Equi Joins as well as other enhancements to the join tool.  Here are two other, older, ideas that share some of the same needs:

• Conditional Joins in Alteryx - Alteryx Community
• Various enhancements to Join Tool - Alteryx Community

Problem:
In large Alteryx workflows, renaming a column using a Select tool (or similar) causes downstream tools like Formula, Filter, Join, Sort, etc., to break or throw errors if they reference the old column name.
This means every time a column is renamed, I have to manually go through every tool that used the old name and update it - which is tedious, time-consuming, and error-prone.

Proposed Enhancement:
Add a feature to automatically propagate field name changes across all tools in the workflow that reference the renamed field.

This could work similarly to how modern IDEs allow you to "rename variable" across an entire codebase, or how refactoring works in platforms like Tableau Prep or Power BI.

Benefits:

• Saves time when making structural changes to data schemas.

• Reduces human error caused by missing updates in some tools.

• Makes workflows more scalable and easier to maintain.

• Improves usability and development speed for all users, especially in large or collaborative workflows.

When building join operations in Alteryx, it can be time-consuming to manually scroll through long lists of fields to find the right one to join on, especially when working with large datasets or unfamiliar schemas.

It would be great to have a search-as-you-type filter in the Join tool’s field selection interface. Similar to the existing field selector search, this feature would allow users to start typing a field name and instantly see a filtered list of partial matches. This would significantly speed up the process of identifying and selecting the correct join fields and reduce the risk of selecting incorrect fields due to visual clutter.

Allow the user to select which type of Join is happening in the join tool. Allow the J anchor to output the join type within a radio option in the tool configuration. Right now the J anchor outputs matches in both inputs. New suggestion would allow the user to select a Right Outer or Left Outer join without having to use a Union tool after the Join.

The Join Tool tells you which records did not match (Left and Right) but it does not tell you what fields it did not match on.  This could quickly help the analyst determine which fields they need to look into to determine why there are unmatched records.  When joining on 5+ fields it becomes difficult to determine why some records did not match without manually inspecting each record which is time consuming.  The column title could be: Unmatched Field(s) and the values should be concatenated separated by commas.

For tools that have 2 input or output anchors, the ability to right-click the tool and select "swap connections" to swap the connections coming out of the 2 input or output anchors. Let's say, with 5 connections coming out of the Unique's U Anchor and 3 coming out of the D anchor, we need to disconnect and reconnect connections 8 times in order to switch everything between U and D. Even if the tool has 1 connection per anchor, this would still be faster than removing + connecting each connection manually. Tool examples include Append data, Join, Unique, Macros with 2 inputs or outputs, etc.

Hello

Cartesian product is a common issue when joining dataset with a bad key. What I suggest is an option to check if there will be a cartesian product on the join tool.

-there is a label "Cartesian product (non join key uniqueness) detection"
-under it a drop down menu with three choices
-do nothing
-fail
-warning

Algo :
if do nothing==> well... do nothing more than actual behaviour.
if "fail" or "warning" : count distinct of join key versus count row on each side of the join. If none is unique, display a warning or an error message.

Best regards,

Simon

The Find and Replace tool currently replaces text or appends fields by reviewing the first word in the string and evaluating if there is a matching value in the lookup replace table. If there is, it returns the replace text or it continues onto the next word in the string and repeats until a match is found or not found.

However the functionality should be that the entire string is evaluated and the first match in the replace lookup table is returned. This is similar to how the VLOOKUP formula functions in Excel. 

This makes workflows easier to maintain as the ordering of the lookup table can be amended to return the appropriate value. The ordering of the source string is far trickier to automate/change. 

I have seen workarounds where append fields is used to append all lookup values to each row and then find rows which contain the string and use a sample tool to return the first row, however this causes processing issues when working with large number of records/ lookup values.

The functionality should either be changed or at least a setting which can be toggled to switch between the two options.

Have Alteryx have different colored lines for the different paths that come in and go out from a tool like how Excel colors references in a formula.

I would love a tool to be created for looking up a value in a table based on a condition. It could be called "Lookup." One input to the tool would be the lookup list, the other is the main database. Inside the tool you could enter functions that can query the lookup table and return the results either as an overwrite of an existing field in the main DB or as a new field in the main DB, similar to the options in the Multi-Row Formula tool.

Here is a link to my post in Community that explains the problem. The solution, in a nutshell, was to create a Join (which resulted in millions of additional rows), run the conditional formula, then filter to get rid of the millions of rows that were created by the Join so only those that met the condition remained (the original database rows).

Here is the text of my Community post describing my project (slightly modified for clarity):

Table 1:  A list of Pay Dates (the lookup table)

Table 2:  Daily timekeeper data with Week Start and Week End Date fields.

The goal:  To find the Pay Date in Table 1 that is greater than the Week Start Date in Table 2 and no more than 13 days after the Week End Date in Table 2.

[Table 2: Week Start Date] < [Table 1: Pay Date]

and [Table 2: Week End Date] < [Table 1: Pay Date]

and DateTimeDiff([Table 1: Pay Date], [Table 2: Week End Date], 'Days') <= 13

There are many different flows I could use this type of tool for that would save time and simplify the flow.

Thanks!

I think it would be great to have a tool that allows you to update a dataset with another dataset. For example, this could be used in updating an archive table on a daily basis as data changes. Having a tool available that streamlines this data operation would be helpful to simplify workflows.

In the tool, you would be given the option to select your primary key fields, which are the fields used to identify records. Additionally, you have the option to perform an insert, modify, or delete operation, according to the primary key fields that you choose in the configuration.

Obviously this is something that anybody could create a macro for if they wanted to. But it would be nice to have a tool in place so that we dont have to worry about it. I think this would be a nice use case to bolster Alteryx usage as a data engineering tool for relational database management in particular.

One of the most frequent issues I have with my workflow is when the Join Tool reorders columns for no reason. There is an option in the configuration to have the columns revert to the incoming order. It would be nice if the workflow automatically had this set as a default, or provide a toggle option in the settings for this to happen. In many of my flows I've had to create macros to keep the integrity of the column order or remember to go into the settings of this tool and re order the columns before a workflow run. 

The Find Replace tool has a checkbox to do a case insensitive find. It would be fabulous if the Join and Join Multiple tools had a similar checkbox.

I frequently have to create a new field in each data stream, convert the data I want to join on to upper case, perform the join and remove the extra "helper" fields. Using the helper field is needed in my case in order to preserve unique capitalization (i.e., acronyms within the string, etc.). 

Having the ability to specify columns to be "coalesced" (essentially filled in automatically when records aren't present) across all inputs of the Join Multiple tool would be very useful, and save lots of tedious setup. In fact, I avoid that tool if possible, currently, because of the extra steps necessary in maintaining key column integrity after the join.

In the screenshot below "RecordID" is present in all 4 inputs. When they are joined together, however, not every record is present in all inputs, therefore each of the 4 "RecordID" columns has nulls. We then have to go through an extra step to find the first "non-null" record ID, and put that in a new column (or in the first "RecordID" column).

This could be simply fixed by adding a menu to specify columns that should be coalesced, or auto-filled from other inputs if it's missing in the first one.

The columns in the menu could be based on the first input, and then alteryx would look for columns with the same name in all other inputs and complete the logic below, for instance:

If isnull([RecordID]) then

      if isnull([Input_#2_RecordID]) then

           if isnull([Input_#3_RecordID]) then

                    [Input_#4_RecordID]

           else [Input_#3_RecordID] endif

     else [Input_#2_RecordID] endif

else [RecordID] endif

Hi,

I was thinking that this might be nice addition - while Joining two inputs at the bottom there is always possibility to check if we want to include Unknown columns. Maybe we could specify if we want to join Unknown columns only from Right Input or Unknown only from Left Input. I know I would use this in my workflows. 

Thanks

Alicja

I want a feature to enable join by custom conditions. Currently, in Join tool, allowed condition is only equality of specific fields and specific position, however, in SQL, we can join data by much more flexible conditions like;

SELECT TableA.id FROM TableA INNER JOIN TableB ON TableA.id=TableB.id and TableA.value > TableB.value  

Of course, my idea can be easily realized by using combination of Appendix Field + Filter tool, but I meant to say is that Appendix-Fields is quite expensive operation in calculation cost, and it would generate many unnecessary records, which is annoying us in case of handling a huge dataset.

I suppose this kind of flexible conditions can be specified by using expression editor, thereby configuration window of this feature would look like the below image; Adding one more radio button option, and expression editor similar to one used in Filter tool.

Any positive/negative feedback on my idea would be appreciated. Thank you for your attention!

hi team,

Currently the join tool doesn't have option to join the data irrespective of its case sensitivity. this causes issues while doing the analysis.

This tool is widely being taking as a replacement of Excel Vlookup formula (which is also case insensitive),so can we have the similar functionality for Join tool also?

We can have an option like a radio button or checkbox to select if we want to have case insensitivity while joining.

Thanks

As an international organization we deal with clients in multiple-countries.

Name matches for names including Chinese characters generate a unicode conversation warning and are excluded from the fuzzy match.

It would be good if fuzzy match could be enhanced to handle Chinese characters.

I would like Alteryx to offer a native Fuzzy Join tool that allows two datasets with completely different schemas to be joined using Fuzzy matching logic (Dice coefficient algorithm, Levenshtein distance algorithm, etc.). Any matches would be output to a new table with either exactly matched or fuzzy matched primary and secondary records. I want this tool be supported by Server as well.