Joining data and assuming nulls

Question

Hi Everyone,

So I have 2 data sets that look like this:

CityEQPTMonthYearCompletion FactorCIN7876201784.82%FIN7376201796.68%BIN74762017116.05%HOM7176201773.38%AUS73762017113.97%ATL7876201798.14%ATL78762017100.41%

CityEQPTMonthYearTripsMinutesHoursMilesCIN78762018753823151371.918451605FIN7376201954657.752513BIN7476201715614875247.91780558HOM7176201813315475257.91792117AUS7376201913714950249.16784109ATL78762019704789401315.666433632ATL78762017667710351183.917399953

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 Answer

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.