Review Join Results in the Data Grid


After you have created a join on the canvas, review the data grid to make sure the join produces the results that you expect. If the data grid displays data that you don't expect, you might need to modify the join.

Results in the data grid

  • No data: If no data displays in the data grid, you might need to change the join type or a join field used in the join condition. If you suspect a mismatch between fields in the join, use a calculation instead. For more information, see Use calculations to resolve mismatches between fields in a join.

  • Duplicate data: If you see duplicate data, there a few things you can do. Consider relating the tables instead of joining. For more information, see Relate Your Data(Link opens in a new window).

  • Missing data: If some data is missing from the data grid, you might need to change the join type or a join field used in the join condition. Again, if you suspect a mismatch between fields in the join, use a calculation instead. For more information, see Use calculations to resolve mismatches between fields in a join.

  • Many null values: If you see many null values that you do not expect, you might need to change the join type from the full outer type to the inner type.

  • All null values for one table: If all values for one table is null, there are no matches between the tables that you are joining. If this is not expected, consider changing the join type.

Use calculations to resolve mismatches between fields in a join

When the fields in a join condition don't match—that is a mismatch between the values in the fields used in a join condition, the data grid can show little or no data at all. A mismatch between fields can occur for several reasons but often caused by the differences in format of the string values or date values in the fields. In many cases, you can resolve mismatches between the fields in your join by using a calculation.

Most functions are available for you to use in a calculation to create and replace a field in the join condition, with the exception of aggregate functions and table calculation functions.

Note:Join calculations are not supported for QuickBooks Online, Marketo, Oracle Eloqua, Anaplan, ServiceNow ITSM, and web data connectors.

String mismatch

A common mismatch scenario when working with string data occurs when one of the fields on one side of the join condition is equivalent to two or more fields on the other side of the join condition. In this case, you can use a calculation to combine the two fields so that its format matches the other field in the join condition.

For example, suppose you want to join two tables that contain the following data:

Patron Contact
First name Last name Branch Member since Units borrowed Fees Suggested limit
Alan

Wang

 North 2000 1 0 15
John Smith North 2000 36 3.50 15
Ashley Garcia South 2000 243 11.30 15
Fred Suzuki North 2000 52 .90 15
Name Member number Emergency contact Relationship Emergency number
Adam Davis 555-0324 Ellen Davis Partner 555-0884
Alan Wang 555-0356 Jean Wilson Mother 555-0327
Fred Suzuki 555-0188 Jim Suzuki Brother 555-3188

Henry Wilson

 555-0100 Laura Rodriquez Partner 555-0103
Michelle Kim 555-0199 Steven Kim Partner 555-0125

The common fields between the two tables appear to be name. However, in the Patron table the first and last names are in separate columns and in the Contact table the first and last names are in the same column. To join the tables on names, you can use a calculation in the left side of the join condition to merge the first name and last name columns together.

The result is a calculated field on the left side of the join condition that is accessible only from the join dialog. This calculation converts the field in the Patron table into a format that now matches the format of the field in the Contact table on the right side of the join condition.

Using the calculation in the join produces the following combined table: 

First name Last name Branch Member since Units borrowed Fees Suggested limit Name Phone number
Alan Wang North 2000 1 0 15 Alan Wang 555-0356
Fred Suzuki North 2000 52 .90 15 Fred Suzuki 555-0188

Date mismatch

A common mismatch scenario when working with date data occurs when the date values in one field of the join condition are captured at a different level of detail than the other field in the join condition. In this case you can use a calculation in the join condition to change the format of the field on one side of the join condition so that its format matches the other field in the join condition.

For example, suppose you have the following two tables of data:

Projector rental Patron
Date Reservation type Requester ID

1/1/2000

 Individual 233445589
1/28/2002 K-12 365948999
1/29/2002 Non-profit 233448888
12/5/2002 K-12 365948999
5/5/2003 Non-profit 334015476
3/12/2004

Non-profit

 334015476
3/15/2006 City 211896980
7/8/2007 K-12 334015476
1/4/2008 Individual 560495523
3/8/2009 Non-profit 233445566
2/14/2014

Non-profit

 233445566
12/21/2015 Non-profit 233445566
2/10/2016 Non-profit 233445566
ID First name Last name Branch Member since Units borrowed Fees Suggested limit
454613981 Adam Davis West 2012 25 0 10
232502870 Alan

Wang

 North 2000 1 0 15
298000916 Amanda Smith North 2001 54 6.4 15
233445566 Ashley Garcia South 2000 243 11.30 15
900005122 Brian Frank East 2011 12 .10 10
921491769 Elizabeth Johnson West 2010 19 .5 10
233445589 Fred Suzuki North 2000 52 .90 15
344556677 Henry Wilson South 2005 3 .2 15
939502870 Jane Johnson West 2017 0 0 10
233448978 John Smith North 2000 36 3.50 15

To find out more information about new patron behavior, joining the Patron table to the Projector Rental table might provide some insight about which library services motivate new memberships. The common fields between the two tables appear to be "Date" and "Member since." However, the date values in each field are captured at different levels of detail. To join these tables on their respective date fields, use a combination of DATE functions in a calculation on each side of the join condition to make the level of detail in each field match.

DATE(DATETRUNC('year', [Date]))

DATE('01/01/'+STR([Member since]))

Using the calculation in the join produces the following combined table:

Date Reservation type Requester ID ID First name Last name Branch Member since Units borrowed Fees Suggested limit

1/1/2000

 Individual 233445589 232502870 Alan Wang

North

 2000 1 0.00 15
1/1/2000 Individual 233445589 233445589 Fred Suzuki North 2000 52 0.90 15
1/1/2000 Individual 233445589 233445566 Ashley Garcia South 2000 243 11.30 15
1/1/2000 Individual 233445589 233448978 John Smith North 2000 36 3.50 15

To determine if a patron rented the projector in the same year he or she started his or her membership, add one more clause to the join based on ID.

The result of the additional join condition shows that only one patron might have started his membership to rent a projector.

Date Reservation type Requester ID ID First name Last name Branch Joined Units borrowed Fees Suggested limit
1/1/2000 Individual 233445589 233445589 Fred Suzuki North 2000 52 0.90 15
Back to top
Thanks for your feedback!Your feedback has been successfully submitted. Thank you!