Matching Issue - Join and Union Tools

[dandreas]

Hi Community People,

Maybe a simple solution but having some trouble. We only have two columns in two datasets: Account number and Account balance.

We initially had four datasets - three from last year and one from two years ago. The three from last year were unioned with the Union tool by name which worked out. 

We are now trying to combine the two datasets (One unioned dataset from last year with the dataset from two years ago) into one dataset and would like the output datasets placed right next each other in Excel and are now matched by account number. So the first row would have the same account numbers from each dataset on columns next to each other (but the account balances will be different which is fine). The second row would have the same account number from each dataset with different balances, and so on for every match. We tried accomplishing this with the Join tool and, then, Union the L, J, and R outputs together but only the J dataset shows up in the output of the Union tool. The L and R datasets are missing. What do we do to keep all datasets going forward but still match by account number in the workflow?

The main issue seems to stem from sometimes there is no account number match in the unioned dataset to the dataset from two years ago and vice versa. This is expected and we want all variations to show up in the final output. Do we need to replace the nulls with zero at some point earlier or later in the workflow, create a unique identifier and label the rows with no account number match from two years ago and a different unique identifier with the account numbers from two years ago but none in the unioned dataset, or how do we achieve keeping all variations in the final output? 

Screenshots attached for your reference.

[LindonB]

For some reason, I can't see the images, but I think I have a sense of what you're trying to accomplish. I would approach it like this...
1. Union last years' datasets together.
2. Join two year's ago to last years with a full outer join.
3. Sort the combined table as needed.
4. Add a Record ID field, as this will ensure that you don't need to replace the nulls with 0 be the two tables will have the same number of rows.
5. Use two select tools: one to choose the Record ID and the fields from two years ago and the other to choose the Record ID and the fields from last year.
6. Use a block until done with one output going to an Output tool to write the table to a specific range. The second block until done output should go to another Output tool to write the table to the other specific range next to the first table. 

Alternatively, you could always just write one table after the join and some sorting though and compare the numbers there.

[Bren_Spill]

Hi @dandreas - see the attached workflow. I have included 2 different approaches.

1) Your described approach should work assuming the columns are actually labelled XYZ.... and ABC.....

2) If the columns are named Account Number and Account Balance as per your description above, then you'll need to use something like the second approach attached.

If this helps, please mark as solution. Thanks!