This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 02-03-201712:37 PM - edited on 04-22-201909:48 AM by SydneyF
One common reason fewer records than expected come out of a join can be due to mixed cases within the data of the join fields between the two datasets. For example, and as shown below, "Orange" from the first dataset is not going to join to "orange" in the second dataset. And this does not just apply to the leading character. Another common reason can be due to the existence of trailing or leading spaces. As also shown in the example below, "Plum " is not going to join with "Plum". The little red triangle in the upper right-hand corner of "Plum" indicates that there are leading and/or trailing spaces.
A simple method to get around this is to attach a formula tool after each Input tool and use the following formula for, in this example, the field "Fruit":
This will convert all records for this field in each dataset to upper case, while also removing any leading or trailing spaces, allowing for a more successful join. If you are joining by multiple fields, then simply use the Multi-Field Formula Tool to convert all of the fields.
In a related issue, you might find yourself getting too many records than you were expecting. If this is the case, check out this Community article about inadvertently getting duplicates when joining.