|Linking Tables and Worksheets ||
When linking tables or worksheets, rows are matched up using link columns.
It's pretty obvious that we cannot link tables by simply putting them next to one other and gluing them together. For one thing, they probably don't have the same number of rows. Even if they did happen to have the same number of rows, there's no guarantee that the rows correspond to one another. Obviously, the tables must be combined by intelligently matching up rows. This is typically done by comparing the information in a specified column (or columns) in one table with information in the corresponding column(s) in the second table. So, for example, if both tables contained names, we could match up the tables' rows based on names. We call such columns link columns.
So far, so good. But what if some names appear in one table and not in the other? What if names appear more than once in each of the tables? Let's take a more realistic example:
These two tables both contain a Customer ID column so we can use that column to match their rows. But note that:
The result of linking these tables together depends on whether you are linking the Customer table into the Sales table or vice versa. The more typical thing to do is the former, so let's do that first. Suppose you were looking at the Sales table and you linked the Customer table to it. Here's what you would get:
Now let's see what happens when you do the reverse and link the Sales table into the Customer table (i.e., you are looking at the Customer table and link in the Sales table):
Note that simple linking will never change the number of rows in your table. If you are looking at a table that has one million rows and you link in another table that has one thousand rows, the number of rows in your view will still be one million. Likewise, if you are looking at a table that has one thousand rows and you link in another table that has one million rows, the number of rows will still be one thousand. (For those familiar with the SQL database language, this is essentially a modified left outer join.) There is, however, an action that combines linking with row selection (comparable to the SQL inner join.) This action, link and select rows, can reduce the number of rows in your view.
In the above examples, rows were matched based on one column (Customer ID) in each table. Links can also be based on more than one column, in which case rows are matched if the values in all the corresponding link columns are the same.
It is also possible to match rows whose link column values are related but not exactly the same. In 1010data, these are called as-of links.