End Totals Don't Match Start Totals when Trying to Merge Data

This is a two part question.

I want to combine/merge/join budget files. File #1 = 2017. File #2 = 2016. The real data continues with a file for each year back to 1995.

What is the best approach? Starting with #1, I want to append #2016 quantities and dollars to records that appear in both. Next, I want to add any records that do not appear in file #1 to the merged set. I then want to repeat the process with the result of #1 and #2 with #3, etc.

End totals don't match start totals.

The approach in the attached sample seems to work. The totals add of everything except this subset of the data. For this subset, I can't get the end total to match the start total. I suspect the problem is associated with the CT field and the L values found in some of the records. The core set of data only has A, B, and C values under CT. This subset is the only one that may include L, N and E.

The filters attached each year pull out the records associated with a specific budget line. This line has L values. The values these filters sum to the correct total. The value after the join does not. ( I need the output of the join to still breakout the budget line against CT.)

Thanks

merge_data_sets.yxzp

sample_4_alt_quest_actuals_16.xlsx

sample_4_alt_quest_actuals_17.xlsx

Join

Accepted answers

DavidP

I notice that the join condition is Id_Line = Id_Line, but if you connect a Unique tool to the output of each of your Select tools that precede the join tool, you can see that Id_Line is not unique for each of 2016 and 2017.

At the output of the 2016 line's Select tool, there are 46 records, so you'd want to see 46 records after the Union Tool, but in fact there are 50. This happens because of the duplications in your Id_Line fields.