Join

This analytic test combines two separate datasets into one unified dataset. This test is useful when you need to combine two datasets because one dataset holds master data which enriches the insights you can derive from the second transactional dataset. For example, you could use this test to identify vendors with no transactions.

Note that the two datasets being joined to are referred to as dataset A (the primary file) and dataset B (the secondary file).

This test supports the following joins:

  • Inner Join (Retains only the rows that have matches in both datasets A and B)

  • Outer Join (Retains all rows that have matches in either dataset A or B, or in both)

  • Left Outer Join (Retains all rows from dataset A, and the matching rows from B)

  • Right Outer Join (Retains all rows from dataset B, and the matching rows from A)

  • Records with no secondary match

  • Records with no primary match

This analytic test can be used to:

  • Verify that all vendor transactions are properly recorded. A vendor master data file ensures that all vendors are documented, reducing the risk of missing transactions.

  • Confirm that vendor information is accurate. This helps ensure that payments are made to legitimate vendors and that errors in vendor information are minimized.

  • Ensure the vendor master data file is maintained in accordance with regulatory requirements and company policies is crucial for compliance purposes. Auditors verify compliance with relevant laws, regulations, and internal policies.

Fields used for analysis

The following fields are required for this analysis:

Core column(s):

  • Primary File:

    • One or more fields that will be used as the match

    • One or more fields to be included in the joined result.

  • Secondary File:

    • One or more fields that will be used as the match.

    • One or more fields to be included in the joined result.

For the two join fields there must also be an indicator if the join should be performed in an ascending or descending order. The fields from the primary and secondary files must match as they will be used as the key for the join.

Parameters

One parameter must be set in order to run the test:

Join Type:

  • Inner Join (Retains only the rows that have matches in both datasets A and B)

  • Outer Join (Retains all rows that have matches in either dataset A or B, or in both)

  • Left Outer Join (Retains all rows from dataset A, and the matching rows from B)

  • Right Outer Join (Retains all rows from dataset B, and the matching rows from A)

  • Records with no secondary match

  • Records with no primary match

Test configurations

The only configuration available for this test is Transactions with no supplier account.

Technical specifications

When you run the Duplicates analytic test, the following steps are performed to run the test:

  1. Obtain if this is a final result or an interim result.

  2. Obtain the type of join being performed from the following types:

    • Inner Join (Retains only the rows that have matches in both datasets A and B)

    • Outer Join (Retains all rows that have matches in either dataset A or B, or in both)

    • Left Outer Join (Retains all rows from dataset A, and the matching rows from B)

    • Right Outer Join (Retains all rows from dataset B, and the matching rows from A)

    • Records with no secondary match

    • Records with no primary match

  3. Obtain the primary file:

    1. Obtain the matching field(s)

    2. Obtain the list of fields that will be included in the result

  4. Obtain the secondary file:

    1. Obtain the matching field(s)

    2. Obtain the list of fields that will be included in the result

  5. Obtain if the matches are in ascending or descending order or a mix.

  6. Perform the proper join based on the user selections.