This guide describes how to apply the Join columns from 2nd Layer analysis to merge your current dataset with another existing dataset, by linking a shared row value.
For example, admin regions typically contain a unique ID, such as FIPS for US Counties. Suppose you have two map layers containing FIPS data. You can join a single value, or multiple values, from the admin region data into a selected map layer. The Join column from 2nd layer analysis option merges the shared value into the second dataset.
Let's explore demographic data using a US Presidential Election Map of 2016. This example joins a county-level census map layer to a county-level election result map layer.
Import the template .carto file packaged from "Download resources" of this guide and create the map. Builder opens with election data as the first map layer, and education data by race as the second map layer.
From the Election_2016 map layer, click the ANALYSIS tab.
Click ADD ANALYSIS and apply the Join column from 2nd layer option. The source layer must be the layer from which the final geometry needs to be derived.
Select the education by race county map layer as the INPUT #2 dataset.
Select Inner as the JOIN TYPE.
For the Foreign Keys options, select the
fips column for both datasets. This joins the FIPS columns from both datasets.
For the Columns options, select the columns from each dataset that you want to keep in the final, joined dataset. For this example:
Note that since raw numbers are not an accurate representation for demographic data, columns that contain percentage data for education are selected to be added to the Election dataset.
Click APPLY to join the data.
The final map layer includes all the data from the Election_2016 dataset, and the selected columns from the Education dataset.
View the Election_2016 map layer in the Data View and note the four new columns that were added to your table, all prefixed with "right" in the column name. The prefix is significant of the SQL join clause in a relational database. The first table, or the source table, is referred to as the left table. The second table on which the join is performed is referred to as the right table, hence the prefix right for the column name.
If you are interested in using the underlying functions in the SQL view of Builder, you can join common columns from your datasets with the SQL
Inner Join and
Left Join queries. View the SQL Join syntax for details.
We would love to hear from you! Was it easy to understand? Do you need more information? Let us know.