Table functions for combining tables

These functions are used to combine fields of two tables by matching the values of common attributes (text or numeric). They are among the most powerful and versatile table functions. You can use them:

  • For adding new attributes, or attribute combinations into tables (attribution, or attribute enrichment). For example, you can add a unique product_id into a table, for each unique product_name-color pair (1 to 1 relation). Or you can add a new attribute category into table, which is mapped each product_id (1 to N relation). Both cases are demonstrated in the examples below.
  • For adding new key figures into tables along with new attributes. For example, you can add a new key figure for average sales per category into a table with product ids and categories.
  • Generally, you can add new attributes and key figures into tables for calculating the values of some other key figures through row-by-row processing of table (see table transformation functions). For example, you could add margin into a table with the key figure costs, before calculation price where price = costs x (1 + margin).

Adding key figures of Table2 to Table1

Following conditions must be satisfied in order to use the table function CombineKeyFigures:

  • Table1 and Table2 must have no common key figures.
  • Table2’s attributes must be a subset of Table1’s attributes. That is, Table1 must contain all the attributes of Table2.
  • Table2 must have distinct (unique) attribute rows; that is, each row of table must have a different attribute value combination.

Cost table: costs per product name and color

In the following example, new key figures margin and discount from margin table are added to cost table by matching the values of the common attribute category in both tables.

Margins and discounts per category

// default key figure values for unspecified categories
var DefaultKeyFigValues = new Dictionary();
DefaultKeyFigValues["margin"] = 0.22;
DefaultKeyFigValues["discount"] = 0.11;
// initiate index vectors for matched rows
NumVector MatchedRowsTbl1, MatchedRowsTbl2;
// combine key figures
MatrixTable CombinedKeyFigTbl1 =
MatrixTable.CombineKeyFigures(CostTable, MarginTable1,
DefaultKeyFigValues, out MatchedRowsTbl1, out MatchedRowsTbl2);
// view table
MatrixTable.View_MatrixTable(CombinedKeyFigTbl1,
"Combined table; margins & discounts per category")

Combined cost table with margins and discounts per category

Note that default margins and discounts (0.22 and 0.11) are assigned to the products from the category Camera in the table above, because margins and discounts for this category were not specified in the margin table.

By row-partitioning the resultant combined table with the row indices returned by the function, you can get the sub-table with matched rows only (i.e. rows with categories that also exist in margin table):

// view subtable with matched rows only
MatrixTable.View_MatrixTable(
MatrixTable.PartitionRow(CombinedKeyFigTbl1, MatchedRowsTbl1),
"Combined table; margins & discounts per category - matched rows only")

Combined table with matched rows only

In the following example, margins and discounts are specified in more detail, namely per product and year. In this case, these fields (product and year) are common attributes of the tables to be combined.

Margins and discounts per product and year

// default key figure values for unspecified categories
DefaultKeyFigValues["margin"] = 0.22;
DefaultKeyFigValues["discount"] = 0.11;
// combine key figures
MatrixTable CombinedKeyFigTbl2 =
MatrixTable.CombineKeyFigures(CostTable, MarginTable2,
DefaultKeyFigValues, out MatchedRowsTbl1, out MatchedRowsTbl2);
// view table
MatrixTable.View_MatrixTable(CombinedKeyFigTbl2,
	"Combined table; margins & discounts per product and year")

Combined table: Margins & discounts per product and year

Note that default margin and discount values (0.22 and 0.11) are assigned to the row with the attribute combination Toshiba-2008, because this pair was not specified in the second margin table above.

Combining fields of two tables

CombineTables is a generalized version of the table function CombineKeyFigures explained above (seen the other way around, CombineKeyFigures is a special case of CombineTables. Or seen the other way around, CombineKeyFigures is a special case of CombineTables.

Note that both table functions are not commutative; that is, f(Table1, Table2) is not the same as f(Table2, Table1). The output table contains all the fields and rows of the first operand, plus additional fields from the second operand.

With CombineTables you can combine not only the key figures, but all fields including the attributes (text and numeric) of tables, by matching the values of common attributes in each row. Following conditions must be satisfied in order to use this table function:

  • Table1 and Table2 must have at least one common attribute; numeric or text
  • Table1 and Table2 must have no common key figures
  • Table2 must have unique attribute rows (unique table condition)

Following example shows:

  • How the independent attribute pair product_name and color in the cost table above can be mapped to a unique product_id
  • How new key figures margin and discount can be added to cost table along with the text attribute product_id

Cost table; costs per product name and color

New margin table below: Margins and discounts per product id; the attribute pair product_name-color is mapped to a unique product_id

Margins and discounts per product id

// combine tables: Add unshared fields of MarginTable3 to CostTable2
// no default values for unshared fields are specified explicitly for unmatched rows.
var CombinedTbl2 = MatrixTable.CombineTables(CostTable2, MarginTable3,
	null, null, null, out MatchedRowsTbl1, out MatchedRowsTbl2);
// view table
MatrixTable.View_MatrixTable(CombinedTbl2,
	"Combined table 2: Margins/Discounts/Costs per ProductID")

Combined table: Margin/Discount/Costs per product id

Let’s add product categories to the combined cost table above. For that purpose, we will use the table below as input which maps each product id to a product category.

Category map table

// default category for product ids whose categories are not specified
var DefaultTextAttribValues = new Dictionary();
DefaultTextAttribValues["category"] = "Undefined";
// combine tables
var CombinedTbl3 = MatrixTable.CombineTables(CombinedTbl2,
	CategoryMapTable, DefaultTextAttribValues, null, null,
	out MatchedRowsTbl1, out MatchedRowsTbl2);
// view table
MatrixTable.View_MatrixTable(CombinedTbl3,
	"Combined table 3: Category is added to table")

Combined table: Category is added into table

Notice that Undefined is assigned to product id BLC3 (blue camera) as category because a category for this product id was not specified in the category map table.

Now, assume that we have average sales per category in another table as shown below. Following example illustrates how this additional information can be added to the combined cost table above.

Average sales per category

// combine tables
var CombinedTbl4 = MatrixTable.CombineTables(CombinedTbl3, AvgSalesTable,
	null, null, null, out MatchedRowsTbl1, out MatchedRowsTbl2);
// view table
MatrixTable.View_MatrixTable(CombinedTbl4,
	"Combined table 4: Average sales per category added to table")

Combined table: Average sales per category added to table

Note that the default value 0 is assigned to the category Undefined as it was not specified in the sales table above.

Copyright secured by Digiprove © 2012 Tunc Ali Kütükcüoglu

Leave a Reply