Table of Contents

Search

  1. Preface
  2. Introduction to Data Validation Option
  3. New Features and Behavior Changes
  4. Repositories
  5. XML Data Source
  6. Tests for XML Data Sources
  7. Connections
  8. Expressions
  9. Table Pairs
  10. Tests for Table Pairs
  11. Single-Table Constraints
  12. Tests for Single-Table Constraints
  13. Examples of Tests from Spreadsheets
  14. SQL Views
  15. Lookup Views
  16. Join Views
  17. Aggregate Views
  18. Business Intelligence and Reporting Tools Reports
  19. Dashboards
  20. DVOCmd Command Line Program
  21. Troubleshooting
  22. Datatype Reference
  23. Reporting Views
  24. Metadata Import Syntax
  25. Jasper Reports
  26. Glossary

Data Validation Option User Guide

Data Validation Option User Guide

Join View Example

Join View Example

You need to validate the inventory sales done by the employees and partners to cross-check with the annual sales report.
Account table in an SAP system holds the information of an employee account. Partner table is a Salesforce table that contains the information of the inventory sold to a partner associated with an employee. Inventory is a flat file that contains the details of the inventory sold. Account_History is an Oracle table that contains the history of activities done by the account.
Current requirement is to validate data across the tables based on the inventory sales of an account. You also need to validate the account details with the historic account details to check for discrepancies. You can create a join view with the tables so that you can run single table tests to validate data.

Tables and Fields

The following table lists the join view tables and their columns:
Table
Columns
Account (SAP)
Account table contains the following columns:
  • Account ID
  • Account Name
  • Collection
  • Inventory
Partner (Salesforce)
Partner contains the following columns:
  • Partner ID
  • Partner Name
  • Inventory
  • Cost
  • Associated Account ID
Inventory (Flat file)
Inventory table contains the following columns:
  • Inventory ID
  • Quantity
  • Associated Partner ID
  • Associated Account ID
Account_History (Oracle)
Account_History contains the following columns:
  • Historic Account ID
  • Account Name
  • Total Inventory
  • Total Collection

Creating the Join View

  1. Enter Account_Cumulative as the description.
  2. Add Account as the first table in the join view.
  3. Add Partner, Inventory, and Account_History tables in that order.
  4. Configure the table definitions with the required join types.
  5. Create join conditions for Partner, Inventory, and Account_History.

Table Definition Configuration

The following list describes the tables and their join types when you configure the table definitions:
Partner
You want to capture the details of partners associated with each account. Configure an inner join for the Partner table so that Data Validation Option adds the details of the partners for which there are corresponding accounts to the join view.
Inventory
You want to capture the details of the inventory sold by the partners. Configure an inner join for the Inventory table so that Data Validation Option adds the details of the inventory for which there are corresponding partners to the join view.
Account_History
You want to capture the historic details of an account. Configure a left outer join for the Account_History table so that Data Validation Option adds all the historic account details to the join view.

Adding Join Conditions

Configure the following join conditions for the tables:
Partner
Select Account as the join table. Select Account ID output field from the Account table as the left field and Associated Account ID output field from the Partner table as the right field of the join.
Inventory
Select Partner as the join table. Select Partner ID output field from the Partner table as the left field and Associated Partner ID output field from the Inventory table as the right field of the join.
Account_History
Select Account as the join table. Select Account ID output field from the Account table as the left field and Historic Account ID output field from the Account_History table as the right field of the join.
The following figure shows the formation of the join view with the table relationship:
The Account, Partner, Inventory, and Account_History tables are joined together to create the join view.
After you create the join view, create a single table with the join view. Generate and run tests on the single table to validate the data in the join view.

Removing Table from the Join View

After you run the required tests for the join view with all the tables, you may want to remove the partner information and run tests solely for the account. If you remove the table Partner from the join view, the join condition for the table Inventory is no longer valid. You need to create another join condition for the table Inventory to save the join view.
The following figure shows the broken join view:
The Account and Account_History tables are joined together to create the join view. The Inventory table is not connected in the join view because it is not included in a join condition.
Add a join condition to the table Inventory with Account as the join table. Join Account ID field in the Account table with Associated Account ID field in the Inventory table.
The following figure shows the join view without the Partner table:
The Account, Inventory, and Account_History tables are joined together to create the join view.