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

[hellyars]

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

[DavidP]

Could you please post examples of the 2 input files?

[hellyars]

Are the two files not in the packaged workflow?

[DavidP]

The package only seems to contain the alteryx workflow

[hellyars]

Okay.  That's funny.  But any ways,  I uploaded both files. 

[DavidP · Accepted solution]

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.

I think for your logic to work, Id_Line has to be unique in both 2016 and 2017.

Perhaps add something else when you construct Id_Line to ensure they are unique.

[hellyars]

That was how I was able to work around it.  Id_line is not meant to be a unique ID here.  But, I was able to work around the issue (as you describe) by making it unique.  So, it sounds like make a unique Id to work around this and I can back it out/hide it later. 

Thanks. 

[DavidP]

Yes, if my understanding of how you want to merge the files are correct, the fields on which you join the 2 should be unique to ensure no lines are joined to multiple lines from the other year.