Join With Keys

Introduction

In order to be able to use a lot of the statistical tools comparing data from different sources, we need a way of “bringing them together”. Many of the tools require the variables we may be interested in modelling or comparing or computing need to be in the same dataset in order to be recognised.

The tabular left outer join does this by taking two tables and creating a join based on a specific key or variable. In the vast majority of instances, this key or variable will be the specific area codes – the variable which identifies which SLA or SA1 or LGA is which. It is therefore vital to remember that you can only join datasets that are at the same geographical level of aggregation

In setting up a join we simply specify which table will be on the right side of the new table, which table will be on the left, and join them according to the area code. Unlike the tabular inner join tool, this join will keep all additional rows in the left hand table that may not have corresponding rows in the right hand table.

Inputs

To show the Tabular Left Outer Join tool in use we will combine datasets from the Northern Territory and Australia

  • Select Northern Territory as your area
  • Select the following datasets, with all attributes:
    •  Demographic Variables by SLA for Australia
  • Select Australia as your area
  • Select the following datasets, with all attributes:
    •  Socio-economic variables by SLA for Australia
  • You will now have two different datasets – one containing only Northern Territory records, and the other containing records for all of Australia.
  • Open the Tabular Left Outer Join tool (Analyse → Tools → Data Manipulation → Tabular Left Outer Join) and enter the following parameters (shown below):
    •  Left Side: Socio-economic variables by SLA for Australia (this is your larger dataset, covering all of Australia)
    •  Left Attribute: sla code
    •  Right Side: Demographic Variables by SLA for Australia (this is your smaller dataset, covering only the Northern Territory)
    •  Right Attribute: sla code
  • Once you’ve entered your parameters click Add and Run to execute the tool

It is important to remember that your specific identifier columns may have different names. These naming conventions are the outputs of the data custodians – it is worthwhile to open each of the datasets prior to joining them and identify the column which contains the keys so that you know which ones to include in the inner join.

[Click to Enlarge]

 

Outputs

Once you have run your tabular left outer join, click the Display button on the pop-up dialogue box that appears to view your joined data (it should look something like below). You should see the left hand part of the table has rows for all of Australia with socio-economic data for every SLA. However, the right hand side of the table (demographic data) will only have populated rows for the Northern Territory.

It will appear in your data panel named Output: left-outer-join XXX. It is a very good idea to rename this to something more meaningful, as more often than not it will be this dataset which will be used in most of your subsequent work. You might also wish to join additional datasets together, and you need to do them sequentially, so renaming them allows you to remember which datasets are contained within your joins.

[Click to Enlarge]

[Click to Enlarge]