Alteryx Designer Desktop Ideas

The JOIN tool could use some love.  Let's consider merging the JOIN and UNION functions into a single tool.  Instead of strictly L, J, and R outputs, we could have an option to allow for all standard SQL joins:

 

• Cross Join (Warning!!!)
• Inner Join (boring)
• Left Outer Join (saves time configuring Union)
• Right Outer Join (saves time ...)
• Full Outer Join (saves time ...)

Being able to JOIN on case-insensitive values is a big bonus (resisted urge to BOLD and change font size).

Being able to JOIN on date-range is often requested.

Being able to JOIN on numeric-range is often requested.

 

If we are combining tools, getting UNIQUE on L or R (or both) inputs would also save time.  Most JOIN errors are because the incoming (R) data contains duplicates by KEY.

 

cheers,

 

Mark

 

Hi @NicoleJ 

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

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!

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.). 

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!

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.

I think it would be nice to be able to more easily reorder fields that you're joining by in the Join tool.

 

 

For example, I have already joined by CASS_Address and CASS_City. After I did this, I realized I wanted to go back and join on Name, too, and I want that to be first. How the tool is configured now, if I want Name to be first, I must redo all of the drop downs. I would like to be able to add Name to the next set of open drop downs then use some arrow buttons to be able to move them up in the order (similar to the Summarize tool).

We build some pretty robust maps with multiple connections and it would be great to copy the map tool and paste it with all of the connections when we want to tweak the map slightly but keep our original map.  It is a regular occurrence for us to have a very detailed map grouping by trade area name and then may want to have an overview map with all of the same connections but slightly different layout.  Tracking down the connections, reconnecting them and naming them accordingly takes a substantial amount of time even in the most organized of workflows.  This function would be a huge time-saver.  It would also be of value with joins and unions - anywhere you have multiple streams coming in.

Alteryx is unlike many BI tools in the sense that it joins NULL. It is difficult to think of another platform that has this behaviour. Either people know about this and work around it or they don't and their joins are a ticking time bomb. Please add a check box to the Join and Join Multiple tools to allow or prevent joining NULL. This will serve to remove the need for workarounds as well as educate users about this default behaviour.

On the UNION tool, allow for deselecting columns that aren't relevant.  Leave the union exactly as it is, and you could go into the manual configuration. Align the columns just as you would in the manual configuration.  The addition would be that you have the behavior like you see in a join tool where you could deselect C1, C2, C3.... Cx. 

 

Too many times I have a union and there are fields I simply don't even want to bring in, but then have to add a select tool right after in order to remove them. 

Please build individual *Unknown fields, one from the Left and one from the Right, into the Join tool. One *Unknown field cannot cover both side of the Join leading into the J output.

I would like to be able to use the join tool to join on inequalities.  We could join two tables, A and B on A.value is >= B.value1 AND A.value <= B.value2.  This would replicate the "between" function in SQL.  The equvalent feature in Tableau is pictured below.

Here is the issue I have, when you are using a Join tool and you have multiple columns that you are joining on (to the point that they don't all show in the 
Configuration window), i have a tendency to use the mouse scroll wheel to move down to see additional columns i am joining on.  The mouse scroll controls different things depending on where your cursor is.  If your cursor is over the Left or Right columns then the scroll button will change the Fields you are using to join on.  I have messed up more workflows then i care to mention due to this.  I do not think it is appropriate for the scroll wheel to effect and change the fields in the configuration window and it should only be used to scroll up and down in the configuration window.  

 

 

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. 

 

Have you ever used a Join tool with several (or many) Join fields, looked at the the L and R outputs and wondered, why didn't these records join? When there are many columns in your data, this can be a hard question to answer. It would be very handy if Alteryx could somehow report the Field(s) that each record failed to join on (perhaps as an optional added field to the L and R outputs).

To add the capability to hard rename the columns in all modes 

On the "Join Tool"  allow to click on a connection and say “switch L & R” connection.  Currently if only one connection is there you can move to the other, but if they're both there, you have to disconnect one, and then 'switch'.   

 

I'd say that 95.437% of the Joins I do are straight Inner Joins.

 

So each of those times I have to remember to go down to the Select part of the Join tool and deselect all the fields I joined on the Right Side since they'll be duplicates.

 

I'd like a checkbox like below (defaulted to CHECKED)  to deselect all the joined fields from the right hand side. In the rare cases where there's a need I could uncheck it.

 

Deselect R join fields