Join is one of the most powerful capabilities within IDEA Data Analysis Software as it provides an easy to use and flexible way to combine multiple databases into a single dataset. It is used mainly for two reasons:
- Combining fields from two databases into a single database for analysis.
- For testing data which matches or does not match across databases.
You can join or match databases only if they contain one or more common fields, referred to as “match key fields”. The match key fields do not need to have the same name or length, but they must be of the identical field type.
There are a few important points to consider whilst using the Join task:
- You can define up to eight matching keys.
- You can join only two databases at one time, but a previously joined database can be joined with another.
- IDEA joins the secondary database to the primary (open) database, so make sure to combine the databases in the correct order.
- The databases must be stored in the same location, for example, your Desktop project folder.
IDEA provides five Join options. Be careful to choose the correct one. You can select:
- Matches only; which outputs only those records with matches in both databases.
- Records with no secondary match; which extracts records from the primary database that have no matches in the secondary database.
- Records with no primary match; which extracts records from the secondary database that have no matches in the primary database.
- All records in the primary file; which extracts all records from the primary database with or without matches in the secondary database.
- All records in both files; which extracts all records from both databases.
The Join task takes each record for every match key field in the primary database and looks for a matching record in the secondary database. Join does not support many-to-many relationships, however, so when you join two Character fields of different lengths, IDEA will pad the shorter field with trailing spaces.
The Join task is one of the key facilities within IDEA that an Auditor should master. For more tips and tricks to using IDEA Data Analysis, check out our Technical Tips & Guides.