Getting started with Table Functions

We strongly recommend users of finaquant® protos (non-commercial) or finaquant® calcs (commercial) to download the respective Microsoft Visual Studio project FinaquantProtosStarter or FinaquantCalcsStarter (see related downloads at the respective product page) because it is an ideal training and development tool with its demo functions.

List of demo functions in FinaquantProtosStarter
Getting_started_from_zero()
Getting_started_with_finaquant_protos()
Define_fields_hierarchies_MetaData()
Create_MatrixTable_manually()
Create_TestTable_by_combination_1(out md, out CostTable)
Create_TestTable_by_combination_2()
Create_TestTables_by_combination_3()
Combining_tables()
Export_Import_MatrixTable_XML()   
Table_element_row_column_operations()
Assign_values_to_field()
Table_arithmetics()
Table_transformation_functions()
Table_aggregation_functions()
Matrix_functions()
Vector_functions()
Allocation_Distribution_demo()
Filtering_tables_with_conditions()
Filtering_tables_by_dates()
Sampling_tables_for_target_dates()
Row_by_Row_processing_of_table()
Transforming_subtables()
Function_Router()
Matrix_Function_Router()


Demo functions in Visual Studio project FinaquantProtosStarter

1) First create cost and margin tables

Call the corresponding helper function to create the cost and margin tables that are required for the following examples.

// call helper fuction to create cost and margin tables
// that are required for the following examples
MetaData md;
MatrixTable CostTable, MarginTable1, MarginTable2;
Create_Cost_and_Margin_tables(out md, out CostTable, out MarginTable1, out MarginTable2);

You may see the C# code of this helper function below if you want, but remember: You are a beginner and you don’t need to be concerned with such details at this stage.

Create_Cost_and_Margin_tables()
// Helper method for creating cost and margin tables
public static void Create_Cost_and_Margin_tables(out MetaData md,
	out MatrixTable CostTable, out MatrixTable MarginTable1, out MatrixTable MarginTable2)
{
	// define metadata
	md = MetaData.CreateEmptyMasterData();
	MetaData.AddNewField(md, "category", FieldType.TextAttribute);
	MetaData.AddNewField(md, "product", FieldType.TextAttribute);
	MetaData.AddNewField(md, "brand", FieldType.TextAttribute);
	MetaData.AddNewField(md, "costs", FieldType.KeyFigure);
	MetaData.AddNewField(md, "price", FieldType.KeyFigure);
	MetaData.AddNewField(md, "margin", FieldType.KeyFigure);
 
	// define table structure for CostTable
	var CostTableFields = TableFields.CreateEmptyTableFields(md);
	TableFields.AddNewField(CostTableFields, "category");
	TableFields.AddNewField(CostTableFields, "brand");
	TableFields.AddNewField(CostTableFields, "product");
	TableFields.AddNewField(CostTableFields, "costs");
 
	// create CostTable with elements
	CostTable = MatrixTable.CreateTableWithElements_A(CostTableFields,
		"Notebook", "Ignor", "Ignor UX Notebook", 850.0,
		"Notebook", "Ignor", "Ignor AX Notebook", 950.0,
		"Notebook", "Euphor", "Euphor 5 Notebook", 1200.0,
		"Notebook", "Euphor", "Euphor 10 Notebook", 1450.0,
		"Desktop", "Ignor", "Ignor 4D Desktop", 650.0,
		"Desktop", "Ignor", "Ignor 6D Desktop", 800.0,
		"Desktop", "Euphor", "Euphor 2E Desktop", 1050.0,
		"Desktop", "Euphor", "Euphor 5E Desktop", 1300.0
		);
 
	// define table structure for MarginTable1
	var MarginTable1Fields = TableFields.CreateEmptyTableFields(md);
	TableFields.AddNewField(MarginTable1Fields, "category");
	TableFields.AddNewField(MarginTable1Fields, "margin");
 
	// create MarginTable1 with elements
	MarginTable1 = MatrixTable.CreateTableWithElements_A(MarginTable1Fields,
		"Notebook", 0.40,
		"Desktop", 0.30
		);
 
	// define table structure for MarginTable2
	var MarginTable2Fields = TableFields.CreateEmptyTableFields(md);
	TableFields.AddNewField(MarginTable2Fields, "category");
	TableFields.AddNewField(MarginTable2Fields, "brand");
	TableFields.AddNewField(MarginTable2Fields, "margin");
 
	// create MarginTable1 with elements
	MarginTable2 = MatrixTable.CreateTableWithElements_A(MarginTable2Fields,
		"Notebook", "Ignor", 0.40,
		"Desktop", "Ignor", 0.30,
		"Notebook", "Euphor", 0.45,
		"Desktop", "Euphor", 0.35
		);
}

 

2) View cost table with table viewer

// view cost table
MatrixTable.View_MatrixTable(CostTable, "Cost table");

Cost Table

3) Round numbers (costs) in cost table

We want to demonstrate the merits of table function Round() here, but there is a problem: The numbers are already rounded in cost table.

Let’s first add some fractions through simple table multiplication:

// All key figures are already round!
// Let's add some decimal fractions to costs
MatrixTable CostTable_fractions = CostTable * 1.3333;
 
// view table with fractional numbers
MatrixTable.View_MatrixTable(CostTable_fractions, "Cost table with fractional costs");

Cost table with fractional costs

We can now round the numbers:

// round numbers to 2 digits after decimal point
MatrixTable CostTable_rounded = MatrixTable.Round(CostTable_fractions, 2);
 
// view table with rounded numbers
MatrixTable.View_MatrixTable(CostTable_rounded, "Cost table with rounded costs");

Cost table with rounded costs

Let’s show some examples from table arithmetics that demonstrate the real value of table functions with their built-in logic for attribute combinations.

4) Calculate prices using the first margin table with margins per category

The first margin table defines margins for each category:
First margin table with margins per category

And here is how we get the price table with table multiplication:

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

First price table

5) Calculate prices using the second margin table with margins per category and brand

Second margin table with margins per category and brand

The cost and margin tables can have as many attributes as you want. You could add additional attributes like country, year, product version etc. to cost and margin tables. The logic of table multiplication doesn’t change:

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

Second price table
Here is the end of getting started for beginners. You may want to see more examples with a video in the related article Getting started with finaquant® protos 1.0

Leave a Reply