Allow "Join" and "Join Multiple" tools to coalesce IDs

[ecai]

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

data d1;

      input ID age;

      datalines;

1 45

2 36

3 77

;

run;

data d2;

      input ID height;

      datalines;

1 138

3 176

5 197

;

run;

proc sql;

      select      coalesce(d1.id, d2.id) as id,

                  d1.age,

                  d2.height

      from  d1

                  full join

                  d2

      on          d1.id = d2.id;

quit;

Could you please consider adding this option into the "Join" and "Join Multiple" tools?

[SeanAdams]

Hey @ecai

I'm curious about the output that you're looking for - I've mocked up my understanding of your need below (this is a common-enough need so this is a common pattern)

Data: Similar to yours - data that does not perfectly match

Then we do the full join (which is a Join plus Union in Alteryx)

A full join is accomplished by unioning all the results togetherIn the join - don't bring back both IDs - only one - that makes it easier laterUsing the union tool either in auto or manual mode gives you a unioned set with IDs fully complete

This then gives you the following output:

[ecai]

Hi @SeanAdams,

I'm sorry for the late reply; I had to work on some urgent tasks.  Thank you so much for your detailed reply.

Yes, the above works for the "Join" tool, but choosing only one ID does not work for the "Join Multiple" tool.  Thus, I have to use a subsequent "Formula" tool to get the unified ID with the MAX() function.

MAX([ID1], [ID2], [ID3])

This works, but it would be tedious to do for many IDs, and it would be easier if there was just a coalesce function within the "Join Multiple" tool to do this.

[Community_Admin]

The status of this idea has been changed to 'Inactive'. This status indicates that:

1. The idea has not had activity in the form of likes or comments in over a year.

2. The idea has not reached ten likes.

3. The idea is still in the 'New Idea' status. 

However, this doesn't mean your idea won't be implemented! The Community can still like and comment on this idea. With enough renewed interest, this idea can be brought back into the 'New Idea' status. 

Thank you for contributing to the Alteryx Community and the Alteryx Product Idea Boards!