As-of Links

As-of links allow you to match rows in a less strict way when two tables (or worksheets) are linked together. Using as-of links, you can match a row in the current table with a row in a foreign table whose value is the closest match less than or equal to the value in the current table.

To see what this means, let's start with the Sales table we used in a previous section:

Now, let's introduce the following new table:

This table says that our imaginary store advertised three times. On 7/1 they advertised ties in a newspaper; on 7/10 they took out another newspaper ad, this time for shoes; and on 7/17 they ran a TV commercial for their ties (clearly on a local station in the middle of the night!)

Now suppose we want to answer the question: What was the most recent type of ad before each customer's purchase? (In our small example, this won't tell us much, but in real life this could help a store manager figure out what kinds of ads are most effective.) Before we answer this question, let's see what happens with a normal link. Starting with the Sales table and linking in the Advertisements table, matching the Product and Date columns in each table, gives us:

It's no surprise that the Medium column is all blank since there are no matches between the Sales and Advertisements tables. The Sales table does not have any entries for 7/1 or 7/10 at all, and the only entry for 7/17 is for socks, while the 7/17 entry in the Advertisements table is for ties. So, nothing matches.

Now let's try an as-of link, using the same two pairs of columns, Product and Date:

There were no ads for some of the products, so many of the rows are still blank, but let's take a look at the other rows. Take the sale of ties on 7/16: The closest ad for ties on or before 7/16 was the newspaper ad on 7/1. Similarly, the closest ad for ties on or before 7/18 was the TV ad on 7/17. Now we have the information we were looking for to answer our original question.

What if you wanted to answer the question: What was the first purchase following each ad? You could do this by performing a reverse as-of link. See Example: Reverse as-of links within the <link> topic in the 1010data Reference Manual for an example of how to do this.

As-of Link Guidelines

There are several important things to keep in mind when using as-of links:

  • When linking on multiple columns, as in the example above, the last pair of link columns (in our example, the Date columns) are the ones that are matched inexactly. The other link columns are matched exactly, as in a standard link.

  • It is essential that the rows of the foreign table (i.e., the table that is being linked into the one you are viewing) be in the proper order. In particular:

    1. All rows that have a given value in the first link column must be contiguous.

      In the Product column of our Advertisements table, all of the "tie" entries are contiguous and all of the "shoes" entries (though there is only one) are contiguous.

    2. For a given value in the first link column, all rows that have a given value in the second link column must be contiguous (unless it is the last). A similar rule holds for the third link column, and so on.

      This doesn't apply in our example, because we are only linking on two columns.

    3. For a given set of values in all link columns other than the last, the values of the last link column must be in ascending order.

      In our Advertisements table, for all of the entries that have "tie" in the Product column, all of the corresponding dates in the Date column are in ascending order.

For instance, the following table could not be used in an as-of link (linking on Product and Date, as in our example) since not all "tie" records are contiguous (the "shoes" record is stuck between them).

If a table is not already ordered in a way that follows these guidelines, it can be put into the proper order by sorting the last link column in ascending order, then sorting the next-to-last link column in ascending order, then sorting the next link column, and so on, ending with the first. The table can then be saved as a new table or linked to another worksheet.

Note: Only as-of links require that tables being linked together have a particular order; and, even in that case, only the foreign table needs be in the proper order.

Row Shift

Finally, when using as-of links it is possible to "shift" the match so that instead of matching a row in the current table with the row in the foreign table whose value is the closest match less than or equal to the value in the current table, you can match to the second closest or third closest.

For example, a shift of 1 means: match to the row in the foreign table that follows the row whose value is the closest match less than or equal to the value in the current table. A shift of 2 would match the next row, and so on. If the shift value is negative, the match is shifted backwards. So, for example, a shift of -1 means: match to the row in the foreign table that precedes the row whose value is the closest match less than or equal to the value in the current table.