Examples of join types
Examples follow that illustrate the six types of joins in Analytics. The examples show how you can use different join types to get exactly the information you want in the joined output table.
For a summary view of the six join types, see Which records are included in the joined table?
To use fuzzy matching of key field values to join two Analytics tables, see Fuzzy join.
Sample data
The first five examples use the sample data shown below.
Primary table
Secondary table
Sample data and example details
|
Payroll Ledger table (primary) |
Contains a single pay period and includes all payroll disbursements. One employee, 003, received two checks. |
|---|---|
|
Employee Records table (secondary) |
Maintained by the human resources department. Employee records consist of a complete list of valid employees and the amount that they are paid each period. One employee, 002, does not appear in the table. |
| Join |
In the examples that follow, the Payroll Ledger table is joined with the Employee Records table using the common key field of Employee number. All five examples are many-to-one joins. |
| Objective | In each example, the objective is to test for payroll irregularities. |
Matched primary and secondary records (1st secondary match)
Example
Test You want to verify that employees were paid correctly.
Approach You use a join type that creates one output record for every record in the Payroll Ledger table (P) that has a match in the Employee Records table (S).
Output table Contains all employees who have been paid and who are also listed in the Employee Records table.
Note that the two employee 003 records in the primary table are joined to the same employee 003 record in the secondary table.
Analysis In the output table, you can compare Cheque amount to Pay per period to verify that an employee was paid correctly. Even though employee 003 received two cheques, the total amount of $2000 is correct.
Unmatched primary records
Example
Test You want to find out if someone who is not listed as an employee was paid.
Approach You use a join type that creates one output record for every record in the Payroll Ledger table (P) that does not have a match in the Employee Records table (S).
Output table Contains people who have been paid but who are not listed in the Employee Records table.
Analysis Any record in the output table requires follow-up.
Perhaps employee 002 is a valid employee who has been omitted from the Employee Records table in error, or is listed with the wrong employee number. Or employee 002 may be a phantom employee created as part of a fraud.
All primary records and matched secondary records
Example
Test You want to verify amounts for all checks that were issued.
Approach You use a join type that creates one output record for every record in the Payroll Ledger table (P) whether or not it has a match in the Employee Records table (S).
Output table Contains a complete list of people who have been paid.
Analysis In the output table, you can compare Cheque amount to Pay per period to verify that an employee was paid correctly. You can see that employee 002 was paid $2200 but according to the Pay per period field was not supposed to be paid anything.
Note
Analytics fills missing secondary fields for unmatched primary records with blanks or zeros.
All secondary records and matched primary records
Example
Test You want to verify that all employees listed in the Employee Records table were paid.
Approach You use a join type that creates one output record for every record in the Employee Records table (S) whether or not it has a match in the Payroll Ledger table (P).
Output table Contains a complete list of all employees and what they were paid.
Analysis In the output table, you can compare Cheque amount to Pay per period to verify that an employee was paid, and paid correctly. You can see that employees 004 and 005 were not paid at all.
Note
Analytics fills missing primary fields for unmatched secondary records with blanks or zeros.
All primary and secondary records, matched and unmatched
Example
Test You want to examine all payroll and employee data.
Approach You use a join type that creates:
- one output record for every record in the Payroll Ledger table (P) that has a match in the Employee Records table (S)
- one output record for every unmatched record in either table
Output table Contains all payroll and employee data, whether it is matched or unmatched.
Analysis In the output table, you can compare Cheque amount to Pay per period:
- to verify that an employee was paid, and paid correctly
- to identify people who were paid but who are not listed in the Employee Records table
- to identify employees who were not paid
Note
Analytics fills missing fields for unmatched records with blanks or zeros.
Matched primary and secondary records (all secondary matches)
The example uses the sample data shown below.
Primary table
Secondary table
Sample data and example details
|
Payroll Ledger table (primary) |
The complete Payroll Ledger table has all pay periods and all payroll disbursements for 2018. The example uses January and February disbursements. |
|---|---|
|
Employee Records table (secondary) |
Maintained by the human resources department. The Employee Records table contains:
Two records exist for employee 006:
|
| Join |
In the example that follows, the Payroll Ledger table is joined with the Employee Records table using the common key field of Employee number. The example is a many-to-many join. |
| Objective | In the example, the objective is to test for payroll irregularities. |
Example
Test You want to verify that employees were paid correctly.
Approach You use a join type that creates one output record for every match between records in the Payroll Ledger table (P) and the Employee Records table (S).
Note
Because both source tables in the join contain multiple occurrences of matching key values, you need to use the join type that includes all secondary matches to ensure that you capture all relevant data and derive accurate results.
Output table For each pay date, contains all employees who have been paid and who are also listed in the Employee Records table.
Analysis In the output table, you can compare Cheque amount to Pay per period to verify that an employee was paid correctly for each Pay date.
Because you used the join type that includes all secondary matches (the Analytics many-to-many join), the $200 increase in Cheque amount received by employee 006 starting on 15 February is explained by the matching employee record that shows a $200 raise beginning 01 February.
Remove redundant joined records Depending on the nature of the data being joined, a many-to-many join can create redundant joined records. In the example above, some of the employee 006 joined records contained invalid Pay date-Start date combinations. You can use a filter to remove the invalid combinations and make the output table easier to read:
Emp_Num="004" OR Emp_Num="005" OR (Emp_Num="006" AND Pay_date <= `20180131` AND Start_date = `20150915`) OR (Emp_Num="006" AND Pay_date > `20180131` AND Start_date = `20180201`)
