Table Function Tutorial: Getting Started

Table-valued functions in finaquant .net libraries

Table Function Tutorial: Getting Started

Postby finaquant » 29 Nov 2013, 21:16

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.
Image

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 ©

Image

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 ©

Image

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.

Image

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 ©

Image

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.
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37

Table Function Tutorial: Getting Started - 2

Postby finaquant » 30 Nov 2013, 18:25

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

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 ©

Image

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 ©

Image

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 ©

Image

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 ©

Image

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 ©

Image
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37

Table Function Tutorial: Getting Started - 3

Postby finaquant » 30 Nov 2013, 20:36

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?
Image
User avatar
finaquant
 
Posts: 61
Joined: 16 Sep 2013, 18:37


Return to Table Functions

Who is online

Users browsing this forum: No registered users and 2 guests

cron