Table Function Tutorial: Getting Started

[finaquant]

Getting Started with the Table Functions of .NET libraries finaquant® protos (non-commercial) or finaquant® calcs (commercial)
Audience: C#/.NET developers (beginners included)

Following examples can be found and executed in the Microsoft Visual Studio file FinaquantCalcsStarter with demo functions for training.


Define all fields centrally in MetaData
Fields of all tables must be first defined centrally in a MetaData object.

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. // define metadata
2. md = MetaData.CreateEmptyMetaData();
3. MetaData.AddNewField(md, "category", FieldType.TextAttribute);
4. MetaData.AddNewField(md, "product", FieldType.TextAttribute);
5. MetaData.AddNewField(md, "brand", FieldType.TextAttribute);
6. MetaData.AddNewField(md, "costs", FieldType.KeyFigure);
7. MetaData.AddNewField(md, "price", FieldType.KeyFigure);
8. MetaData.AddNewField(md, "margin", FieldType.KeyFigure);

GeSHi ©

Create Cost and Margin tables manually for the examples below
Before creating a table of type MatrixTable (which can be converted to DataTable of .NET) its fields must be defined.

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. // define table structure for CostTable
2. var CostTableFields = TableFields.CreateEmptyTableFields(md);
3. TableFields.AddNewField(CostTableFields, "category");
4. TableFields.AddNewField(CostTableFields, "brand");
5. TableFields.AddNewField(CostTableFields, "product");
6. TableFields.AddNewField(CostTableFields, "costs");
7.  
8. // create CostTable with elements
9. CostTable = MatrixTable.CreateTableWithElements_A(CostTableFields,
10.     "Notebook", "Ignor", "Ignor UX Notebook", 850.0,
11.     "Notebook", "Ignor", "Ignor AX Notebook", 950.0,
12.     "Notebook", "Euphor", "Euphor 5 Notebook", 1200.0,
13.     "Notebook", "Euphor", "Euphor 10 Notebook", 1450.0,
14.     "Desktop", "Ignor", "Ignor 4D Desktop", 650.0,
15.     "Desktop", "Ignor", "Ignor 6D Desktop", 800.0,
16.     "Desktop", "Euphor", "Euphor 2E Desktop", 1050.0,
17.     "Desktop", "Euphor", "Euphor 5E Desktop", 1300.0
18.     );
19.  
20. // define table structure for MarginTable1
21. var MarginTable1Fields = TableFields.CreateEmptyTableFields(md);
22. TableFields.AddNewField(MarginTable1Fields, "category");
23. TableFields.AddNewField(MarginTable1Fields, "margin");
24.  
25. // create MarginTable1 with elements
26. MarginTable1 = MatrixTable.CreateTableWithElements_A(MarginTable1Fields,
27.     "Notebook", 0.40,
28.     "Desktop", 0.30
29.     );
30.  
31. // define table structure for MarginTable2
32. var MarginTable2Fields = TableFields.CreateEmptyTableFields(md);
33. TableFields.AddNewField(MarginTable2Fields, "category");
34. TableFields.AddNewField(MarginTable2Fields, "brand");
35. TableFields.AddNewField(MarginTable2Fields, "margin");
36.  
37. // create MarginTable1 with elements
38. MarginTable2 = MatrixTable.CreateTableWithElements_A(MarginTable2Fields,
39.     "Notebook", "Ignor", 0.40,
40.     "Desktop", "Ignor", 0.30,
41.     "Notebook", "Euphor", 0.45,
42.     "Desktop", "Euphor", 0.35
43.     );

GeSHi ©

Note that the order of fields in a MatrixTable is important. Fields must be defined and assigned in this order:
TextAttribute, IntegerAttribute, DateAttribute, KeyFigure

Example 1: View cost and margin tables with table viewer

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. // view cost table
2. MatrixTable.View_MatrixTable(CostTable, "Cost table");
3.  
4. // view margin tables
5. MatrixTable.View_MatrixTable(MarginTable1, "MarginTable1");
6. MatrixTable.View_MatrixTable(MarginTable2, "MarginTable2");

GeSHi ©

Example 2: Round all numbers (key figures) in table
In this example we have two table functions:
1) table-scalar multiplication (CostTable * 1.3333)
2) Round all key figures (MatrixTable.Round)

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. // All key figures are already round!
2. // Let's add some decimal fractions to costs
3. MatrixTable CostTable_fractions = CostTable * 1.3333;
4.  
5. // view table with fractional numbers
6. MatrixTable.View_MatrixTable(CostTable_fractions, "Cost table with fractional costs");
7.  
8. // round numbers to 2 digits after decimal point
9. MatrixTable CostTable_rounded = MatrixTable.Round(CostTable_fractions, 2);
10.  
11. // view table with rounded numbers
12. MatrixTable.View_MatrixTable(CostTable_rounded, "Cost table with rounded costs");

GeSHi ©

Example 3: Obtain price table with MarginTable1
This is a classical example for table multiplication. Note that (MarginTable1 + 1) is table-scalar addition; 1 is added to all key figures of MarginTable1

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. // MarginTable1 specifies margins per category
2.  
3. MatrixTable PriceTable1 = MatrixTable.MultiplySelectedKeyFigures(CostTable, (MarginTable1 + 1),
4.     InputKeyFigTbl1: "costs", InputKeyFigTbl2: "margin", OutputKeyFig: "price");
5.  
6. // view PriceTable1
7. MatrixTable.View_MatrixTable(PriceTable1, "PriceTable1: Prices calculated with margins per category");

GeSHi ©

In this example, price margins are determined by category only as given in MarginTable1 above.



Example 3: Obtain price table with MarginTable2
In this example, price margins are determined by category and brand as given in MarginTable2 above.

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. // MarginTable2 specifies margins per category and brand
2. MatrixTable PriceTable2 = MatrixTable.MultiplySelectedKeyFigures(CostTable, (MarginTable2 + 1),
3.     InputKeyFigTbl1: "costs", InputKeyFigTbl2: "margin", OutputKeyFig: "price");
4.  
5. // view PriceTable1
6. MatrixTable.View_MatrixTable(PriceTable2, "PriceTable2: Prices calculated with margins per category and brand");

GeSHi ©

In the examples above, the cost table has only three attributes; category, brand and product. The table functions above wouldn't change even if cost table had 12 attributes and margin tables had 10 of them; all the statements would still be valid.

The only condition that must be satisfied for the table multiplication is this: The attributes of the second table (MarginTable in our case) must be a subset of the attributes of the first table (CostTable). In other words, attributes of the second table must be contained by the attributes of the second table.

The beauty of table functions is in their generality; they can be applied to (almost) any table with any field structure. Once you get used to the generality of table functions, you start to think more about the analytical operation itself rather than the field structure of individual tables.

An analogy: Two matrices of any size can be multiplied (matrix multiplication) provided only that column-count of the first matrix is equal to row-count of second matrix.

[finaquant]

Following examples can be found and executed in the Microsoft Visual Studio file FinaquantCalcsStarter with demo functions for training.


Define all fields and create tables for following examples

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. // define metadata
2. MetaData md = MetaData.CreateEmptyMetaData();
3. MetaData.AddNewField(md, "category", FieldType.TextAttribute);
4. MetaData.AddNewField(md, "product", FieldType.TextAttribute);
5. MetaData.AddNewField(md, "year", FieldType.IntegerAttribute);
6. MetaData.AddNewField(md, "costs", FieldType.KeyFigure);
7. MetaData.AddNewField(md, "price", FieldType.KeyFigure);
8. MetaData.AddNewField(md, "margin", FieldType.KeyFigure);
9. MetaData.AddNewField(md, "total_costs_category", FieldType.KeyFigure);
10.  
11. // define table structure
12. var CostTableFields = TableFields.CreateEmptyTableFields(md);
13. TableFields.AddNewField(CostTableFields, "category");
14. TableFields.AddNewField(CostTableFields, "product");
15. TableFields.AddNewField(CostTableFields, "year");
16. TableFields.AddNewField(CostTableFields, "costs");
17.  
18. // create cost table with elements
19. MatrixTable CostTable = MatrixTable.CreateTableWithElements_A(CostTableFields,
20.     "Computer", "HP", 2008, 1200.0,
21.     "Computer", "Toshiba", 2008, 800.0,
22.     "Computer", "Asus", 2010, 900.0,
23.     "Computer", "Asus", 2011, 1100.0,
24.     "Computer", "HP", 2010, 750.0,
25.     "Computer", "Toshiba", 2010, 950.0,
26.     "Mobile phone", "Nokia", 2008, 300.0,
27.     "Mobile phone", "Motorola", 2008, 250.0,
28.     "Mobile phone", "Nokia", 2010, 200.0,
29.     "Mobile phone", "Motorola", 2010, 150.0
30.     );
31. // view CostTable
32. MatrixTable.View_MatrixTable(CostTable, "Cost table");
33.  

GeSHi ©

Example 1: Calculate prices; margins are given per category only
An example with table-scalar addition and table multiplication

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. // create the first price margin table
2. var MarginTableFields1 = TableFields.CreateEmptyTableFields(md);
3. TableFields.AddNewField(MarginTableFields1, "category");
4. TableFields.AddNewField(MarginTableFields1, "margin");
5.  
6. // margins per category
7. MatrixTable MarginTable1 = MatrixTable.CreateTableWithElements_A(MarginTableFields1,
8.     "Computer", 0.25,
9.     "Mobile phone", 0.40
10.     );
11. // view MarginTable1
12. MatrixTable.View_MatrixTable(MarginTable1, "Margins per category");
13.  
14. // add 1 to margins
15. // Could also be written with this syntax: MarginTable1x = MarginTable1 + 1.0:
16. MatrixTable MarginTable1x = MatrixTable.AddScalarToAllKeyFigures(MarginTable1, 1.0);
17.  
18. // price = cost x (1 + margin)
19. MatrixTable PriceTable1 = MatrixTable.MultiplySelectedKeyFigures(CostTable, MarginTable1x,
20.     InputKeyFigTbl1: "costs",
21.     InputKeyFigTbl2: "margin",
22.     OutputKeyFig: "price",
23.     MultiplyRestWith: 1.0);
24. // view PriceTable1
25. MatrixTable.View_MatrixTable(PriceTable1, "First price table, calculated with margins per category");

GeSHi ©

Example 2: Calculate prices; margins are specified per product and year
This time, price margins are specified by two attributes instead of a single one. Note that number of attributes in tables doesn't affect the formulation of table functions.

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. // create the second price margin table
2. var MarginTableFields2 = TableFields.CreateEmptyTableFields(md);
3. TableFields.AddNewField(MarginTableFields2, "product");
4. TableFields.AddNewField(MarginTableFields2, "year");
5. TableFields.AddNewField(MarginTableFields2, "margin");
6.  
7. // margins specified per product and year
8. MatrixTable MarginTable2 = MatrixTable.CreateTableWithElements_A(MarginTableFields2,
9.     "HP", 2008, 0.25,
10.     "HP", 2010, 0.35,
11.     "Asus", 2010, 0.30,
12.     "Toshiba", 2008, 0.25,
13.     "Nokia", 2008, 0.45,
14.     "Motorola", 2008, 0.40
15.     );
16. // view MarginTable2
17. MatrixTable.View_MatrixTable(MarginTable2, "Margins per product and year");
18.  
19. // add 1 to margins
20. MatrixTable MarginTable2x = MarginTable2 + 1.0;
21.  
22. // price = cost x (1 + margin)
23. // default margin for unspecified product-year pairs: 1.20
24. MatrixTable PriceTable2 = MatrixTable.MultiplySelectedKeyFigures(CostTable, MarginTable2x,
25.     InputKeyFigTbl1: "costs",
26.     InputKeyFigTbl2: "margin",
27.     OutputKeyFig: "price",
28.     MultiplyRestWith: 1.20);
29. // view PriceTable2
30. MatrixTable.View_MatrixTable(PriceTable2, "Second price table, calculated with margins per product and year");

GeSHi ©

Example 3: Aggregate cost table to obtain costs per category
This example is about simple table aggregation: Aggregate all key figures w.r.t. all attributes..

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. // close all open table viewers
2. TableViewerList = CloseAllOpenTableViewers(TableViewerList);
3.  
4. // partition cost table to obtain costs per category before aggregation
5. MatrixTable CostsPerCategory = MatrixTable.PartitionColumn(CostTable,
6.     TextVector.CreateVectorWithElements("category", "costs"));
7.  
8. // view CostsPerCategory before aggregation
9. MatrixTable.View_MatrixTable(CostsPerCategory, "Costs per category, before aggregation of costs");
10.  
11. // aggregate costs with default aggregation function SUM
12. CostsPerCategory = MatrixTable.AggregateAllKeyFigures(CostsPerCategory, null);
13.  
14. // view CostsPerCategory after aggregation
15. MatrixTable.View_MatrixTable(CostsPerCategory, "Total costs per category, after aggregation of costs");

GeSHi ©

Example 4: Insert a separate key figure total-costs-per-category into cost table
In this example, not all key figures but a selected key one (costs) is aggregated with respect to a selected set of attributes (category). A new key figure named total-costs-per-category is inserted for keeping aggregated values.

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)

1. MatrixTable CostTableAdj = MatrixTable.AggregateSelectedKeyFigure_B(CostTable,
2.     RefAttributes: TextVector.CreateVectorWithElements("category"), InputKeyFigName: "costs",
3.     OutputKeyFigName: "total_costs_category", AggrOpt: AggregateOption.nSum);
4.  
5. // view CostTableAdj
6. MatrixTable.View_MatrixTable(CostTableAdj, "Cost table with added key figure total costs per category");

GeSHi ©

[finaquant]

Visit also Anatomy of Table Addition to understand the general logic of table functions. There is a corresponding Demo Function in the MS Visual Studio file FinaquantCalcsStarter.

Examples included:

• How to add a scalar number to all key figures of a table
• How to add a scalar number to a selected key figure of table
• How to add a scalar number to a selected key figure of table with result in another key figure
• How to add up common key figures of two tables
• Using match-all attribute values (joker values) for table addition
• How about other algebraic operations like multiplication, division and subtraction?