Linking columns of tables on closest match

Sometimes it is necessary to combine tables on a common column that do not contain exact matching information. You can combine tables using an asof link that matches rows with their nearest value.

Difficulty

Objective

You want to combine the columns of two tables, linking based on a common column. However, the values in this common column are not an exact match from table to table. For example, you have a table containing sales information and another table containing inventory data, and you would like to combine the columns of the tables based on date. While your sales table contains daily entries, your inventory table contains weekly entries, therefore you want to match the nearest week of inventory to each entry in your sales table. This can be done in 1010data by performing an as-of link.

Solution

<base table="pub.doc.retail.altseg.sales_detail_transid"/>
<sel value="sku=211611 & store=85"/>
<tabu label="Tabulation on Sales Detail" breaks="trans_date">
  <tcol source="qty" fun="sum" name="sum_sold" label="Sum of`Qty Sold"/>
</tabu>
<link table2="pub.doc.retail.altseg.inventory_store" col="trans_date" 
col2="date" suffix="_inv" type="asof">
  <sel value="sku=211611 & store=85"/>
  <colord cols="date,quantityOnHand"/>
  <sort col="date" dir="up"/>
</link>

Alternate solution

<base table="pub.doc.retail.altseg.sales_detail_transid"/>
<sel value="sku=211611 & store=85"/>
<tabu label="Tabulation on Sales Detail" breaks="sku,trans_date">
  <tcol source="qty" fun="sum" name="sum_sold" label="Sum of`Qty Sold"/>
</tabu>
<link table2="pub.doc.retail.altseg.inventory_store" col="sku,trans_date" 
col2="sku,date" suffix="_inv" type="asof">
  <sel value="store=85"/>
  <sort col="date" dir="up"/>
</link>

Discussion

Using Sales Detail for your base table, the desired item SKU and store numbers are selected, and a tabulation is performed to obtain the number of units sold for each day. Within the <link> operation, the same selections must be done on the Inventory table. To effectively preform the asof link, you must then sort the foreign table by date. Your base table must also be sorted by date in the same direction. The base table used in this recipe is already sorted by date in the ascending direction, so the foreign table is sorted to match. With the tables sorted correctly, performing an asof link will bring in the inventory data for the week closest to each date in the Sales Detail table.