Chapter 12: Joins

Chapter 12: Joins Transcript

Sometimes the data you need is spread across two or even more databases. While it’s possible to split your screen to have a horizontal or vertical view of the data, it’s often more convenient to simply join the databases together to make a new file. Here’s how you do it.

Open the database which will be your primary file. In this case, I’m going to use the Summarized Transactions database.

Now open the Customer file and split your screen horizontally. I can see by looking at these files that they both have a customer number field. This is called a key and is the data I’m going to use to establish the join between these two files. In order to do this, I need to ensure that both of these fields are the same data type - meaning that they are both character, or both numeric or whatever. Hover your mouse over the field headers in each file to see the data types. In this case, they are both characters so we can proceed with establishing a join on this field.

Ensure the focus is on the Summarized Transactions database by clicking the header tab and then click the Analysis Tab, and click Join in the Relate group. The Join Databases dialog appears. Ensure that you have Summarized Transactions as your primary database.

In the Secondary database area, click Select to choose which file you are going to join to the Summarized Transactions. We want Customer. Select that file and click OK. You have the option to pick and choose which fields will appear in your output by clicking the Fields button. All fields are selected by default.

Next, provide a name for your new database. I’ll call it Customer Balances.

Next, you need to tell IDEA which field to join the files with. Click the Match button and in the Primary field select Customer Number. Do the same in the Secondary field. Note that the field names do not need to be the same, but the data type does. Click OK to return to the Join Databases dialog.

Lastly, you need to define what kind of join you want IDEA to make. There are five possibilities which are best explained by hovering your mouse over each option and studying the Venn diagrams and explanations which appear. Select the All records in both files option and click OK to complete the join. Your new file should have 351 records.

I mentioned the History earlier in this video series and said that it keeps a record of everything which is done to a database. Let’s open that up and see what it looks like as well as some of the other information you can get from it.

With the Customer Balances as the active file, click History in the Properties pane. Expand each section of the log and see what kind of information is available.

This is where we joined the two files. I can see that there are 351 records, that I have 10 unmatched records in the primary file and 48 unmatched records in the secondary file. The primary file was summarized transactions and the secondary file was the Customer Database. This means that there are 10 records in the Transactions file for customers who are not in my Customers file. The 48 unmatched secondary files are not as alarming – they indicate customers who have no transactions in this period.

Have a look at the IDEAScript code section of the history. If you need to perform this join task again, you can use this section of code to create a macro to do so.

Click Data in the properties window to return to the data view. You’ve used the history to learn more about your data and now you want to examine the data to see the unmatched records.

Find record number 9. Note that CUST_NO1 field is empty. IDEA has a feature called “Display all records containing” which will help you identify all similar records.

Right click in the empty space under Cust_no1 and select Display All Records Containing empty brackets. A dialog box appears. This dialog works much like setting filters in the equation editor but provides an interface to help build the equation. Click OK. You now have a subset of your data showing the 10 records which didn’t have a corresponding entry in the customer-database file. Note that the criteria you built in the dialog box is also visible in the Properties pane.

If you wish, you could save this file. For now, I’m going to return to the complete data set. Right click on the criteria link in the properties window and select clear. Repeat the process of isolating records by finding all customers with no sales in the period. Right click on any record in the AMOUNT_SUM column. Ensure the Operator is the == and make sure the value entry is 0.00. Click OK. You should have 49 records, as indicated at the bottom of the IDEA window.