Sign in or start a free trial to avail of this feature.
6. Finalizing the Merge
In order to proceed with our query merging, we need to create a unique ID column. We can do this by merging columns together, which we will see in this lesson.
- Merging creates a new column which combines the contents of several separate columns
- You have the option to retain the existing individual columns, or replace them
- Delimiters are used to separate the individual column entries in the merged column
- Merging columns can be used to create a unique identifier for a table that does not have one
- Merging queries combines two or more datasets by matching entries in one column from each table
- The merged query will include all the columns from both tables
In the previous lesson, we began the process of merging our Sales Data query with the new Quantity query. However, we found a need to create a column that uniquely identifies each row in our query. In this lesson, we'll create such a column in both of our queries, and then complete the merge. Let's start by creating the unique column in the Quantity query. We'll navigate to the queries pane in the left of the window, and select the Quantity query. Each row in this query measure the quantity of an individual product sold to an individual costumer on an individual date, therefore, we can create a unique ID by merging the Date, Product, and Customer ID columns. We'll hold the control key and select these three columns.
We can merge columns from the Transform tab, or from the Add Column tab. Using the Transform tab, the new column will replace the existing columns. Using the Add Column tab, the new column will be created separately, and the existing columns will be retained. We want to keep the existing columns, so we'll select the Add Column tab, and then Merge Columns.
We now need to choose a separator, or the character that will be placed between the three values. We'll choose a space. Let's also take the opportunity to name the column. We'll call it Unique ID, and then press OK to create the column. The new column combines the date, product name, and costumer ID for each transaction, all separated by spaces. Since the column has a unique value in each row, we can use it to merge our queries. Let's now move on to our Sales query. Before creating a unique ID column here, we'll move the Date column to the left of the table. We'll right-click on the column name, select Move, and To Beginning.
Now this query has the same layout as the Quantity query. This ensures that our Unique ID column will have the same layout in both queries. Again, we'll select the Date, Product, and Costumer ID columns, navigate to the Add Column tab, and select Merge Columns. Once again, we'll use the space as a separator, call the column Unique ID, and press OK.
We're now ready to merge our two queries. To accomplish this, we'll navigate to the Home tab, and select Merge Queries.
We'll select Quantity as the query we want to merge with, and select the Unique ID column in both tables as the basis for our merge. If we look at the bottom of the preview window, we can see that all rows are matched. Moving on, we'll click on the drop-down menu below, and see that there are several different kinds of Join available. We'll stick with the Left Outer Join. This takes all the rows from the Sales query and adds the matching data from the Quantity query. Any data from the Quantity query that does not match the data from the Sales query is left out. We'll press OK to complete the merge.
We can see that the Quantity query has been added in a new column, taking the form of a table. We'll expand the table by clicking the arrows, and choose which columns we want to keep. We only need the Quantity column, so we'll uncheck Select All Columns, and check Quantity.
We'll also uncheck the original column name as prefix option and select OK.
Our merge in now complete, and each row contains a new entry, with the quantity of each product sold. Through these last two lessons, we've seen how to merge both columns and queries. In the next lesson, we'll move on to look at another common task performed in the Query Editor, filtering.