How to report on primary key in one file and not the other when doing a field comparison

[pderwa12]

I have a simple workflow where I am comparing one file to another file in order to see the changes that were made between the two files.  Alteryx is only picking up the records where there is a match on the primary key. I want the output to show me all the changes, even if there is no match on the primary key.  What do I need to do to fix this.  Here is a snapshot of my workflow.  Is there a formula I can add that will show all primary key fields even if they do not all exist in the new file or vice versa?

[Luke_C]

Hi @pderwa12 

You can add a union after the join tool to bring the fall outs back into your dataset.

https://help.alteryx.com/20214/designer/union-tool

[binuacs]

@pderwa12 The unmatched records can be found in the L and R anchor of the join tool. As @Luke_C  mentioned use the union tool to connect the L and R anchor records along with matching records (J- anchor)

[pderwa12]

I do not know how to configure the union tool to print the fallout of the report when doing a comparison between two files.  Can you give and example.  Here is what I currently have:

[binuacs]

@pderwa12 please go through the below post

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-to-Combine-Join-Output-Anchors-...

[pderwa12]

I added the union tool and connected the left, right and join to it from the 'join' tool.  I am not sure how this helps to include the fallout in the output file, when there is no match between the primary keys.  Meaning their primary key in one field is only in one file and not the other.  

[binuacs]

@pderwa12 Can you provide some sample data and expected result? 

[pderwa12]

Should I create a separate workflow or should I add another formula to show whether there is or is not a match between the two files instead of trying to include it in the output file that just shows me where the primary keys match.  I am so sorry, I am still understanding how to configure this.  I did go to the link and still do not understand.  

[binuacs · Accepted solution]

@pderwa12 attaching a sample workflow which tell which rows are matching, 

[pderwa12]

This is what I am seeing in the current output file:  The output file only shows the records where there is a match between the PO number in both files.

PO Number                 Total 03112022     Total 04082022    Variance Between Day Total

PO20134326               $200,000              $200,000              0

PO21123422               $500,000              $300,000               200,000

I would like the output file to include the PO number and value, even if the PO number is not in the other file:

PO Number                  Total 03112022    Total 04082022    Variance Between Days Total

PO22091235                200,000                200,000               0

PO22123632                500,000                300,000               200,000

PO12632112                 0                          225,000              -225,000

PO21123432                 700,000               0                          700,000                   

To spin this in a different direction from the current workflow I have created.  Currently it is showing me all the records, even when there is a match between the PO number/value in one file and the PO number/value in the other file.  This is helpful but it doesn't tell me the whole story.  The only thing I really want to see is the PO number/values when there is not a match between the values and I also want to see the PO and it's value when it does not exist in the other file at all.