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_idinto a table, for each unique
product_name-colorpair (1 to 1 relation). Or you can add a new attribute
categoryinto 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
margininto a table with the key figure
costs, before calculation
pricewhere price = costs x (1 + margin).
Adding key figures of Table2 to Table1
Following conditions must be satisfied in order to use the table function
- 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.
In the following example, new key figures
discount from margin table are added to cost table by matching the values of the common attribute
category in both tables.
// 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")
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")
In the following example, margins and discounts are specified in more detail, namely per
year. In this case, these fields (product and year) are common attributes of the tables to be combined.
// 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")
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.
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
colorin the cost table above can be mapped to a unique
- How new key figures
discountcan be added to cost table along with the text attribute
New margin table below: Margins and discounts per product id; the attribute pair
product_name-color is mapped to a unique
// 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")
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.
// 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")
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.
// 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")
Note that the default value 0 is assigned to the category Undefined as it was not specified in the sales table above.