This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
I've come to realize that the JOIN tool is case-sensitive by design but it would be helpful if you could turn that behavior on/off (via checkbox?) within the JOIN tool. For those of us that work predominantly in database environments that are not case-sensitive, this default behavior has caused me problems many times. Having to force the case to either upper or lower upstream of the JOIN on both flows in order to ensure a successful join is an extra step that would not be necessary if you could disable case-sensitive with a checkbox.
When bringing data together it is often needed to assign a source to the data. Generally this happens when you union data and need to know things later about the data for context. It would save time to generate a source field that is assigned based upon the input connections of the union tool. Perhaps when unioning data you can assign a name to each input stream?
In other data programs like access or Toad you can put conditions on a join. You can choose if something is greater than, less than, left join, right join, etc. But with Alteryx you are only allowed to join a perfect match. It would be really great if you could add that functionality into 9.0
When manually configuring a Union tool within a module/app, once it's saved, if you go in at any point in the future, and change anything upstream from the Union Tool, it causes an error "[some field name] has been changed and the Union Tool needs to be reconfigured".
This happens even if you do not reconfigure any upstream tools, but mearely access them, physically move them, say, an inch to the left or something like that, etc. Basically if you just "touch" any tool upstream, the Union Tool thinks that the manual config needs to be done over. But even if you do reconfig the Union Tool (either delete it and bring a new one in, or change the current one to Auto and then back to manual), it STILL does not recognize the tool as being correct.
The only way I have found to correct this issue is to click Play, and let it throw the error. Add Select Tools before the Union Tool to change the names to be common, then click on the Union Tool, and switch it to auto. Then click Play again. This can be very inconvenient if you have a module that takes hours (although you can limit your input records).
My suggestion would be to either disable the manual configuration, or add notes to the Help Files stating to use the manual configuration at your own risk, because once it's set and works at the time of creation, you cannot modify any upstream tools thereafter or else it will break the Union Tool and it cannot be repaired -- only choice is to use the Select Tool as I described above
I can see that the Venn diagram is very nice for a new user to understand the Join tool (which is a super-great tool by the way). But I would like to be able to close up the Venn diagram to give more room to see the variables listed below.
Problem: When you configure fields manually in the union control and then go back to remove a field it throws up an error that "XYZ field missing" Solution: One needs to change the config to "arrange fields by name" then refresh before you can arrange fields manually
I would like this problem to be handled by some method that tracks for changes and warns the user rather than throw an error.
Today, any Alteryx tool with "Select" functionality has an option for "Dynamic or Unknown Fields" which, when checked, allows any new fields to pass through that tool. This is a great function for most of the tools as you can allow workflow updates to pass through the tool without issue.
However, in the Join tool, there are some use cases where there is NEVER a reason to pass new fields from one side or the other into the tool, but you might still want new fields from a primary process. Examples being something like a lookup/cross-reference to do an inclusive join, where adding new fields to the lookup might inadvertently pass these downstream. Having the option to only allow unknown fields from one side through would greatly enhance this output.
I haven't been able to find a similar idea, but I have a scenario were I'm fully joining a dataset but I get a mismatch of column names.
When using the column renaming functionality in the Join tool, data sent through the Left or Right connector is not renamed. When I'm performing a Full Join the Union tool is adding these columns to the resulting table.
The renaming column functionality of the Join Tool should apply to all join types for consistency. My workaround has been to avoid renaming columns in the Join tool if aiming to perform a multi-join.
I came across the Find Replace Tool when I needed to find values from a column in one table in a column in another table. My first instance to solve the problem was to write a batch macro with a contains function in a formula followed by a not null filter (see attachment). This worked perfectly besides the fact that it was slow. Then I got excited when I discovered the Find Replace Tool accomplishes the same thing WAY faster, but I was wrong.
What I would love is the equivalent of an SQL query like this:
ON A.1 LIKE "%" || B.2 || "%"
which is a legal query in SQLite and is equal to the output of the attached macro. This is what I wish the Find Replace tool could do (Or a different tool), but it only finds one instance per "Find Within Field" value. The tools decision making doesn't line up with the decision-making that I need, for example it doesn't return the longest values found, instead the one with the first key to appear in the field. One way I've found to configure it better is to string a number of these together, that will give me a better result but still won't find every instance and uses 90 or so tools when I feel I should only need 1-3 to accomplish the same thing.
Instead of an Inner Join, the Find Replace is more like of Left Outer Join followed by a Unique() on A.1. Is there a way to accomplish this out-of-database in Alteryx?
In communicating with your agent Eddie Wong about a question (ref:_00DE0JJZ4._50044uMC7T:ref), he confirmed that Alteryx currently does not have an option to coalesce IDs when merging tables in the "Join" or "Join Multiple" tools. This functionality is available in SQL and SAS, for example.
This code demonstrates it.
input ID age;
input ID height;
select coalesce(d1.id, d2.id) as id,
on d1.id = d2.id;
Could you please consider adding this option into the "Join" and "Join Multiple" tools?
Hello, I am very new to Alteryx, so my suggestion might appear elementary for the veteran-users. From the standpoint of adoptation by less technical people (moving from Excel to Alteryx), would it be possible to take common scenarios of Fuzzy Matching and automate them?
For example, if you have 2 databases and you want to match and clean them up, you have to have close to 10 steps, with sorting, adding unique identifiers, joining, fuzzy matching, etc. Just look at your video named "Fuzzy Matching" or other videos for Tableau+Alteryx. They idea is basic. If you know most common use cases and you have already developed a methodology for how to solve these cases, why not have them as part of the tools library?
Also, when matching, for example universities, wouldn't it be easier if all those common, aka appearing many times, words were automatically suggested to be removed from the "match". Example: University A and Univeristy B are two different Universities, but they share the common word "University". While it's important that this word is there, it is not the reason to pair these two together. Maually going through and identifying and typing these common words is an extra step. It would be much easier to have a pop list of these common words and the count of records in which they appear, sorted by the count, and a check box to include or exclude the words.
Link to the Fuzzy Matching video. Check Minute 9:41.