Join tool error?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi there,
I've done a whole lot of data manipulation on a database by:
- Filtering the database field by field to identify line items which do not have data for the fields I'm looking at
- Infilling this data on a field-by-field basis using formulae
- 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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
