Table arithmetics: Addition, multiplication, subtraction, division

With finaquant® libraries (protos or calcs) you can add, multiply, subtract or divide key figures (numbers) of tables. You can also apply all these binary operations on a table with a scalar value. For example, you can add a scalar value to a selected key figure of a table.

Following examples demonstrate the variations of table addition. Apart from the binary operation itself (add, multiply, subtract, divide) applied on tables, the general logic is same for all binary operations. Key feature for all operations is table matching; that is, how rows of two tables are matched to apply a binary operation on key figures of matched rows. Row matching means, finding rows with the same attribute value combinations, as explained below.

Available binary operations

Add/Multiply/Subtract/Divide all common key figures of two tables:

MatrixTable.AddAllKeyFigures(), table1 + table2
MatrixTable.MultiplyAllKeyFigures(), table1 * table2
MatrixTable.SubtractAllKeyFigures(), table1 - table2
MatrixTable.DivideAllKeyFigures(), table1 / table2

Add/Multiply/Subtract/Divide selected key figures of two tables:

MatrixTable.AddSelectedKeyFigures()
MatrixTable.MultiplySelectedKeyFigures()
MatrixTable.SubtractSelectedKeyFigures()
MatrixTable.DivideSelectedKeyFigures()

Add/Multiply/Subtract/Divide scalar values to all key figures of a table:

MatrixTable.AddScalarToAllKeyFigures()
MatrixTable.MultiplyAllKeyFiguresWithScalar()
MatrixTable.SubtractScalarFromAllKeyFigures()
MatrixTable.DivideAllKeyFiguresByScalar()

Add/Multiply/Subtract/Divide scalar values to a selected key figure of a table:

MatrixTable.AddScalarToSelectedKeyFigure()
MatrixTable.MultiplySelectedKeyFigureByScalar()
MatrixTable.SubtractScalarFromSelectedKeyFigure()
MatrixTable.DivideSelectedKeyFigureByScalar()

Creating MetaData and test table

Define meta data and create test table for table arithmeticsFollowing example defines all the necessary table fields in MetaData. A test table with fields category, car, year, costs and sales is also created.

// Define all fields in MetaData
MetaData md = MetaData.CreateEmptyMasterData();
MetaData.AddNewField(md, "category", FieldType.TextAttribute);
MetaData.AddNewField(md, "car", FieldType.TextAttribute);
MetaData.AddNewField(md, "currency", FieldType.TextAttribute);
MetaData.AddNewField(md, "exchg_rate_ref", FieldType.TextAttribute);
MetaData.AddNewField(md, "year", FieldType.IntegerAttribute);
MetaData.AddNewField(md, "price", FieldType.KeyFigure);
MetaData.AddNewField(md, "costs", FieldType.KeyFigure);
MetaData.AddNewField(md, "margin", FieldType.KeyFigure);
MetaData.AddNewField(md, "sales", FieldType.KeyFigure);
MetaData.AddNewField(md, "sales_eur", FieldType.KeyFigure);
MetaData.AddNewField(md, "exchg_rate_eur", FieldType.KeyFigure);
// Define table structure
TableFields tfields1 = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(tfields1, "category");
TableFields.AddNewField(tfields1, "car");
TableFields.AddNewField(tfields1, "year");
TableFields.AddNewField(tfields1, "costs");
TableFields.AddNewField(tfields1, "sales");
<span style="color: #008000;">// create table1 with element values</span>
MatrixTable table1 = MatrixTable.CreateTableWithElements_A(tfields1,
    "Luxury", "Audi AX1", 2008, 250.0, 400.0,
    "Economy", "Audi BE5", 2009, 300.0, 350.0,
    "Sports", "Audi AS8", 2010, 100.0, 150.0,
    "Economy", "Toyota TE5", 2006, 220.0, 250.0,
    "Luxury", "Toyota TX7", 2007, 330.0, 380.0,
    "Sports", "Toyota TS1", 2009, 400.0, 450.0);

Add all common key figures of two tables

This function adds all common key figures of two tables. Following rules must be satisfied so that the operation can be executed successfully:

For a table addition like table3 = table1 + table2:

  • Fields of table2 must be a subset of fields of table1; that is, table1 must contain all the fields of table2.
  • table2 must have distinct (unique) attribute rows; no two rows of table2 can have identical attribute value combinations.

Matching rows of two tablesBefore the binary operation (addition) is applied on key figures, the rows of the tables are matched that have the same attribute value combinations. The figure at the left illustrates this row matching. Note that the first table (input) contains all the attributes (category) of the second table (adjustment). In this example, it is enough to match the category values, because category is the single common attribute.

Matching rows of two tablesIn the second example (see figure at the left), there are two common attributes: category and year. Hence, category-year value combinations are matched before adding the key values of matched rows.

Attribute fields that are used for row matching can be text (like category) or numeric attributes (like year).

Add all key figures. Common attributes: CategoryFollowing example shows how all common key figures (costs & sales) of two tables can be added. The scalar value AddToRestis added to the key figures of unmatched rows.

// create adjustment table
TableFields tfields2 = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(tfields2, "category");
TableFields.AddNewField(tfields2, "costs");
TableFields.AddNewField(tfields2, "sales");
MatrixTable table2 =
MatrixTable.CreateTableWithElements_A(tfields2,
    "Luxury", 2.25, 3.55,
    "Economy", 4.77, 5.88);
// add "sales" and "costs" adjustments for categories "Luxury" and "Economy"
// add 0.0 to rest of (unmatched) rows of table
MatrixTable table3 = MatrixTable.AddAllKeyFigures(table1, table2, AddToRest: 0.0);
// display tables in immediate window
System.Diagnostics.Debug.WriteLine("input table1 = \n" + table1);
System.Diagnostics.Debug.WriteLine("adjustment table2 = \n" + table2);
System.Diagnostics.Debug.WriteLine("add(table1 + table2, 0) = \n" + table3);

The same operation can simply be formulated as follows provided that AddToRest = 0:

// same result can be obtained with following statement:
// add 0.0 to rest of (unmatched) rows of table
table3 = table1 + table2;
System.Diagnostics.Debug.WriteLine("table1 + table2 = \n" + table3);

Add 1.11 to unmatched rows of tableFollowing example adds the scalar value 1.11 to all unmatched rows of table1:

// add 1.11 to rest of (unmatched) rows of table
MatrixTable table4 =
MatrixTable.AddAllKeyFigures(table1, table2, AddToRest: 1.11);
System.Diagnostics.Debug.WriteLine("add(table1 + table2, 1.11) = \n" + table4);

Add all common key figures: Two common attributesUp to this point, the text attribute category was the single common attribute of tables. Let’s add another (numeric) attribute into play, in order to see how row matching works with multiple attributes.

// add another attribute "year" to adjustment table
TableFields tfields5 = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(tfields5, "category");
TableFields.AddNewField(tfields5, "year");
TableFields.AddNewField(tfields5, "costs");
TableFields.AddNewField(tfields5, "sales");
MatrixTable table5 = MatrixTable.CreateTableWithElements_A(tfields5,
    "Luxury", 2008, 2.25, 3.55,
    "Economy", 2006, 4.77, 5.88,
    "Economy", 2006, 4.77, 5.88,
    "Economy", 2009, 1.66, 2.99);
System.Diagnostics.Debug.WriteLine("table5 = \n" + table5);
// add "sales" and "costs" adjustments
MatrixTable table6 = table1 + table5;
System.Diagnostics.Debug.WriteLine("table1 + table5 = \n" + table6);

Add all common key figures: sales as the only common key figureWhat if the second table contains only a subset of key figures of the first table? In such a case, the binary operation (addition) is applied only only the common key figures of both tables. In the example below, sales is the only common key figure.

// obtain adjustment table by column partitioning
MatrixTable table7 = MatrixTable.PartitionColumn(table5,
TextVector.CreateVectorWithElements("category", "year", "sales"));
// add two tables: Adjustments are added to only key figure "sales"
MatrixTable table8 = MatrixTable.AddAllKeyFigures(table1, table7, AddToRest: 3.33);
System.Diagnostics.Debug.WriteLine("add(table1 + table7, 3.33) = \n" + table8);
// add 0 to rest (unmatched rows)
table8 = table1 + table7;
System.Diagnostics.Debug.WriteLine("add(table1 + table7, 0.0) = \n" + table8);

Add selected key figures of two tables

Add selected key figuresThis table function adds only selected key figures of two tables; one key figure from the first, one key figure from the second table. The resultant key figure can be another one.

In the example below (price = costs + margin) the resultant key figure price does not exist in both input tables. Nevertheless, it must be already defined in MetaData.

// create a margin table
TableFields tfields11 = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(tfields11, "category");
TableFields.AddNewField(tfields11, "year");
TableFields.AddNewField(tfields11, "margin");
MatrixTable table11 = MatrixTable.CreateTableWithElements_A(tfields11,
    "Luxury", 2008, 11.11,
    "Economy", 2006, 22.22,
    "Economy", 2009, 33.33);
// apply additive margin
// add selected key figures: price = costs + margin
MatrixTable table12 = MatrixTable.AddSelectedKeyFigures(table1, table11,
    InputKeyFigTbl1: "costs", InputKeyFigTbl2: "margin", OutputKeyFig: "price", AddToRest: 0.66);
System.Diagnostics.Debug.WriteLine("price = costs + margin: \n" + table12);

Multiply selected key figures of two tables

Multiply selected key figuresBelow is another example of operations on selected key figures. The binary operation to be applied is this time multiplication: price = costs x (1 + margin)

// create table with multiplicative margins
MatrixTable MarginTable = 
MatrixTable.CreateTableWithElements_A(tfields11,
    "Luxury", 2008, 0.2,
    "Economy", 2006, 0.3,
    "Economy", 2009, 0.4
    );
// calculate price = costs + (1 + margin)
// margin for the rest: 10%
MatrixTable PriceTable = MatrixTable.MultiplySelectedKeyFigures(table1, MarginTable + 1.0,
    InputKeyFigTbl1: "costs", InputKeyFigTbl2: "margin", OutputKeyFig: "price", MultiplyRestWith: 1.10);
System.Diagnostics.Debug.WriteLine("price = costs x (1 + margin): \n" + PriceTable);

Add a scalar value to all key figures of table

Add scalar value to all key figuresFollowing function adds the given scalar value to all key figures of a table, at all rows.

// add a scalar
MatrixTable table9 = MatrixTable.AddScalarToAllKeyFigures(table1, 2.22);
System.Diagnostics.Debug.WriteLine("add(table1 + 2.22) = \n" + table9);
// same result can be achieved with following statement
table9 = table1 + 2.22;
System.Diagnostics.Debug.WriteLine("table1 + 2.22 = \n" + table9);

Add a scalar value to a selected key figure of table

Following function adds the given scalar value to a selected key figure of a table, at all rows.

// sales = costs + 1.11
MatrixTable table10 = 
MatrixTable.AddScalarToSelectedKeyFigure(table1, 1.11, InputKeyFig: "costs", OutputKeyFig: "sales");
System.Diagnostics.Debug.WriteLine("sales = costs + 1.11: \n" + table10);
Copyright secured by Digiprove © 2012 Tunc Ali Kütükcüoglu

Leave a Reply