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!

[ShankerV]

Hi @eleee3 

The problem is because, the 7 items in the table 1 is joined with just 3 items in the table 2.

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

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

so in the inner join, J node only 3 items will be displayed.

Solution: I will suggest to join both L node and J node with the help of Union tool.

Many thanks

Shanker V

[eleee3]

Hi @ShankerV,

Thank you for your quick reply.

Apologies, I forgot to mention that I am using the Union tool at the end of each process to make the dataset "whole" again and bring the infilled data together.

I am only using Join at the very end of the workflow to bring complete versions of the dataset together in an iterative process, as I've infilled data for about eight different fields and the Join tool can only bring two inputs together at a time. I think the issue is somewhere in this process - where I am using Join-Join-Join one after the other - but I can't figure out why it's an issue.

[ShankerV]

Hi @eleee3 

Any screenshots of your join tool please.

Will help to investigate further.

[Raj · Accepted solution]

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.