Join Renaming

[Alteryxuserhere]

Hi there

Please correct me if I am wrong, when using a join tool, the tool automatically outputs the Right anchors that matches based on the condition and renames the fields to "Right_Fieldname", these arent duplicates of what is in the Left anchor if the output is linked from the "J" anchor but the Right anchor fields are still renamed in the joined output. 

How do I stop this from happening? Setting up formula's to rename the fields again so I have consistent collumns headers is time consuming or am I missing something?

I tried clearing the renames via the "Options" "Clear renames" function but still outputs the renamed collumn headers.

Thanks in advance

[Deano478]

@Alteryxuserhere have you got a sample wf yo can share?

[Alteryxuserhere]

@Deano478 Sure I've attached a simple sample here. Pretty much whenever I use a join tool, it will auto rename the ID to Right_ID and product to Right_Product

[Deano478]

hey @Alteryxuserhere  essentially what it is happening because the field names are the same on both the left and right side and Alteryx is trying to distinguish between them by adding in the right prefix

[Alteryxuserhere]

@Deano478 Thanks for the explanation. So would a Union be more suitable in this instance cause it won't rename the field names? if this is the case when would the Join be more suitable?

[Deano478]

@Alteryxuserhere so you would use the join tool when you want to combine 2 datasets based on common fields whereas in the union you are combining datasets with similar structures. So you have done your WF correctly its just the fact that the field names are the same is all. The union tool in this case will just stack the data so you wont know what matches and what doenst.

[AndrewDMerrill]

@Deano478- is exactly right. The Join Tool should be used when you want to combine two tables horizontally (hence the "Left" and "Right" designations), you can think about putting the tables side by side and connecting rows together that match some criteria. The Union Tool, on the other hand, should be used when you want to combine two tables vertically. There are no criteria involved in the Union Tool (for simplicity's sake); you simply take Table 1 and stack it on top of Table 2.

Another decent rule of thumb (that's not always true, but can help with thinking about each Tool and its use case):

1. Use the Join Tool when you need to add more column to your data
2. Use the Union Tool when you need to add more row to your data

As was mentioned previously, you cannot have two columns with the same column name. So, when using the Join Tool, if two columns have the same name, the Join tools is to designed to add the "Right_" prefix to the relevant columns. You can fix this in two ways:

1. Remove identically named columns from the Join "J" output - which can be done by unchecking the box of the fields you wish to remove.
2. Before the Join Tool add a Select Tool to either input or both inputs and prefix the columns yourself with the data source (e.g. "Data_1.Column_1", and "Data_2.Column_1") This way the identical column names are now different and the Join Tool will not change them further.

Hope this helps add to the conversation and your understanding! Happy Solving!!!