Trying to do a right join and the union keeps picking up the non-selected items

[jogianni]

I am doing a right outer join per the instructions in this link: https://help.alteryx.com/9.5/Join.htm 

unioning the R and J outputs of the merge. 

The two datasets are pretty much identical in terms of variable names. In the merge I have deselected almost everything but the join fields from the left, and the right have been renamed right_[fieldname]. When I go to union the two results, I get what I think are the left fields but they are all null. Either way the field names are repeated but all null. Is there a way to avoid this? (screenshots attached)

[ivoller · Accepted solution]

Hi,

Are you sure the output is all Nulls?

What could be happening is that, since the fields that didn't match from the right are being unioned with the joined fields then you'll see Null values for the right side fields on the joined records. One way around this may be to use the Clear all renames on the Join tool options (assuming left and right names are the same.

Iain

[ghmorrell]

I tried the same thing and it did the same thing to me

The issue is with the R output. Under the J output the fields are renamed "Right_" but under the R Output they are not. If you change your union tool to Manual configure you can figure out what is happening. The non "Right_" will be coming from your R Output.

If you do remove all the renames it will fix most of your fields (apart from those that you do pull from L input). Probably best to manually configure the union. I do that alot just to make sure  i know exactly what it is doing.

This would mean you would only have nulls under the left input fields for where the rest of the fields have come from R rather than J.

Not too sure why you are doing a right outer join if your only pulling the join fields from the left input? if it is for validation see above if not I would just do a R output and ditch the J

Hope this helps

[Treyson]

So I think I know exactly where you are coming from.

The join tool only renames the fields that are going to be joined. If you have a field named Test coming from two data sources the join will be output as Test and Right_Test. However since the data coming from the right part of the join was unaffected by the join, it will continue to be Test, then when you union it, it will be set upon the field named test from the join. Clear as mud right!

What you will need to do is rename the fields from the R output to match what you are renaming them from the join. In this instance you can either use a select tool and type the new names in or use a dynamic rename tool to add "Right_" to the front of the column name. (See Below)

Let me know if this does not work!

The other solution is to switch your input. Since the left defaults to not change it's name, the union would work without renaming everything "Right_")

[Treyson]

To add to my last sentence. I always plan on doing my data with a left join. It makes this union problem easier.

Treyson

[jogianni]

yeah it seems just putting things on the left makes it easier. I'd rather not criscross my lines so much. Is there a way to make the renaming occur to the left side?

[ghmorrell]

Not that I know of. Its best to keep everything as a Left outer join. I only tested it myself because i dont think i have actually ever done a right outer join.

Either this or manual configure the union to what you want it to do

[Treyson]

I believe @ghmorrell is correct. I suppose you could rename it manually in the join. and then type out Left_fieldname manually, but that sounds aweful.

If you don't want to cross the streams

You should definitely check out the dynamic rename tool. It's an under praised tool in Alteryx and can do some really cool stuff.

[ghmorrell]

Actually I meant in the Union tool itself. You can shift the fields with different names like below. I agree nicknaming the left would be awful. But this way you are just using whats already in your workflow with no added tools. In the below Input #2 is the R from a join, #1 is the J. The join fields are "field1"

Hope this clears it up

[jogianni]

actually you're right it wasn't all nulls, I did not know the union would behave like this