How to Include Right Side "L" in Join

[KaraRademacher]

I'm joining current registrant data with previous registrant data.  I'm trying to get a count for the different statuses for current verses prior and compare.  The current data has a new status type that is not coming through when the data is joined.  I can see this spiked out if I click on the 'L' on the right side of the join tool.  How do I include this so that my total of current registrants is accurate?  I included a jpg of the desired output for the Registrant Reconciliation tab.

[caschecter1]

If I understand the problem, you would want to add a select tool after the "L" to rename count to "Current". Do the same on the "R" side and rename count "Previous". Then do a union. Then do a DataCleansing to turn the "null' values into Zeroes. 

[cjaneczko · Accepted solution]

You can add a Union Tool after the Join. Union the J and L of the output anchor and it will bring in the Left side with the Join. 

[nagakavyasri · Accepted solution]

Use Union with L and J of Join Tool:

[KaraRademacher]

I added the union and see this status coming in but it's not pulling the count to Current.  How can I fix this?

[caschecter1 · Accepted solution]

Add a select tool between the "L" output anchor and the union and rename the column "Current". 

[KaraRademacher]

That worked.  Now I want to add a total to the change column.  I added this to the Sum function.  It looks like it should work but isn't coming into the output.  Any suggestions?

[caschecter1 · Accepted solution]

Honestly, it looks fine and is working for me. My results below. One tip though. Because the order of the rows makes a difference to you (you want the totals on the bottom), make sure to check the "Set a specific output order" box in the Union tool. Without it checked, Alteryx can be random about how it puts the inputs to the union together.

Status Current Previous Change

Future Accruing 480 0 480

RFP 3052 3003 49

Inactive 163 158 5

Active 2391 2205 186

Duplicate Record 53 53 0

Retired / PIF's 1603 1546 57

7742 6965 777