How Rows Are Matched Up

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:

The link effectively "imports" into the Sales table all the information about each customer. Since there is no information about customer c0006, the corresponding cells are blank. 
Note: When you do any kind of analysis, the system will automatically deal with N/A (missing) values in a way that makes sense.

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):

For each customer, only the information for the first sale is imported. 
Note: Whenever there is more than one possible row to import, the first row is chosen. 
If there are no rows to import, the cells are left blank (i.e., have N/A values) as in the case of customer c0002.

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.