Join tool error?

eleee3

Hi there,

I've done a whole lot of data manipulation on a database by:

1. Filtering the database field by field to identify line items which do not have data for the fields I'm looking at
2. Infilling this data on a field-by-field basis using formulae
3. Using the Join tool in multiple stages to bring the database together again to (presumably) have the blanks filled in

So the initial database might look something like this:

Crop Ref.	Crop Description	Farmer	Distance to Shop (km)
CRN01	Corn	Joe	10
PLM01	Plum	Lucy	20
POT01	Potato	Anne	5
APP01	Apple		25
CRN02
APP02		Boris
APP03

I'd then identify the empty Crop Description columns to give the following:

Crop Ref.	Crop Description	Farmer	Distance to Shop (km)
CRN02
APP02		Boris
APP03

I would then write formulae to infill the missing information on a field-by-field basis to give something like this for the Crop Description field:

Crop Ref.	Crop Description	Farmer	Distance to Shop (km)
CRN02	Corn
APP02	Apple	Boris
APP03	Apple

I would then do the above steps for the other fields to give outputs as follows:

For Farmer:

Crop Ref.	Crop Description	Farmer	Distance to Shop (km)
APP01	Apple	Boris
CRN02		Joe
APP02		Boris	25

For Distance to Shop (km):

Crop Ref.	Crop Description	Farmer	Distance to Shop (km)
APP01	Apple		25
CRN02			10
APP02		Boris	25
APP03			25

I'm then running multiple iterations of the Join tool to try to bring everything back together and presumably infill the blanks from the original database using the above infilling process.

So the first join for Crop Description and Farmer would give an output like this:

Crop Ref.	Crop Description	Farmer	Distance to Shop (km)
CRN01	Corn	Joe	10
PLM01	Plum	Lucy	20
POT01	Potato	Anne	5
APP01	Apple	Boris	25
CRN02	Corn	Joe
APP02	Apple	Boris
APP03	Apple	Boris

And then another Join between the above table and the infilled Distance to Shop (km) dataset should look like this:

Crop Ref.	Crop Description	Farmer	Distance to Shop (km)
CRN01	Corn	Joe	10
PLM01	Plum	Lucy	20
POT01	Potato	Anne	5
APP01	Apple	Boris	25
CRN02	Corn	Joe	10
APP02	Apple	Boris	25
APP03	Apple	Boris	25

Note that I'm using Crop Ref. (which is unique to each item in my database) to run the Join tool.

The issue is that only some of the blanks are being infilled somewhere in the Join process and others aren't. Any idea why?

Appreciate all your help and thanks in advance!

Raj

please find the solution attached.

also please input farmer for APP03 as it was missing but i have filled it

please let me know if this solves your query.

join solution.yxmd