Joining data and assuming nulls

[Tbrommen]

Hi Everyone,

So I have 2 data sets that look like this:

City	EQPT	Month	Year	Completion Factor
CIN	787	6	2017	84.82%
FIN	737	6	2017	96.68%
BIN	747	6	2017	116.05%
HOM	717	6	2017	73.38%
AUS	737	6	2017	113.97%
ATL	787	6	2017	98.14%
ATL	787	6	2017	100.41%

City	EQPT	Month	Year	Trips	Minutes	Hours	Miles
CIN	787	6	2018	753	82315	1371.918	451605
FIN	737	6	2019	5	465	7.75	2513
BIN	747	6	2017	156	14875	247.917	80558
HOM	717	6	2018	133	15475	257.917	92117
AUS	737	6	2019	137	14950	249.167	84109
ATL	787	6	2019	704	78940	1315.666	433632
ATL	787	6	2017	667	71035	1183.917	399953

What I am attempting to do is join these data sets to add the completion factor column for all rows that match by City, EQPT, Month, and Year.  Then for all of the rows which receive a Null for completion factor (because it did not exist from data set 1), I want to use the previous year (same month and EQPT) completion factor.  Additionally, if the EQPT for a previous year (same month) does not exist, how could I stipulate to use the completion factor for a different EQPT, but in the same Month/Year?

I hope this explained it well enough.

[CharlieS · Accepted solution]

You will need a few Join tools to achieve this, with each subsequent round using the unjoined records from the previous round. I've created the attached example solution to get you started. Also, I didn't know how you wanted to handle multiple matches so I removed the second ATL factor record to simplify the example. 

[Tbrommen]

Thank you Charlie!  I was able to make this work in the way that I needed.  My end result ended up looking like this:

I was really struggling with trying to figure out outer joins, but your example made it click!  Thank you again!