How to write nice and useful table functions (for developers)

Following guidelines and the examples below may help youif you want to write new table functions based on finaquant® protos in order to extend the library.

With a table function here we mean a method that takes a MatrixTable object as the primary input parameter (there can be other input parameters of any type), and returns either another MatrixTable object or nothing (i.e. void). See the example below:

/// <summary>
/// Add scalar value x to all key figures of input table
/// </summary>
/// <param name="tbl">Input table</param>
/// <param name="x">Scalar value</param>
/// <returns>Output table</returns>
public static MatrixTable AddScalarToAllKeyFigures(MatrixTable tbl, double x)

The MatrixTable class of finaquant libraries represents the mathematical model of a table with a simplified data structure compared to DataTable class of the .net framework.

General principles

  • The name of the table function and its use must be intuitive and easy to understand for an average user. Good examples are:
  • Round(): Rounds all key figures of a table.
    Clone(): Returns the clone of input table.
    IsEqual(): Compares two input tables and returns true if all rows are identical within given tolerance limit for key figures.
    ReplaceFieldValues(): Replace all old values of table through new values.

  • Too general or abstract functions may be difficult to understand for some users who think in terms of practical applications.

    Besides, such generic functions are possibly already available in the library, like the table transformation functions. You as a developer can use these generic functions to produce their more user-friendly and application oriented counterparts.

  • There are broadly two kinds of database processing related with tables:
    1. Data processing at entry level: Recording new data, or updating existing data.
    2. Data processing at table level: Analysis and calculations with historical data stored in tables.

    finaquant protos is designed as a library for analytical table computations. Accordingly, the table function you write should be an analytical one; it should serve the second purpose above. The classical SQL based database programming is better suited for manipulating individual entries of tables.

  • Check all the available table functions of class MatrixTable in the library. Don’t rewrite table functions that already exist.
  • All the exception messages must be meaningful and understandable by an average user.

Table function examples

Following salary table is used for the examples below:
Code to create SalaryTable

// Define all table fields in MetaData
MetaData md = MetaData.CreateEmptyMasterData();
md.AddNewField("department", FieldType.TextAttribute);
md.AddNewField("employee", FieldType.TextAttribute);
md.AddNewField("year", FieldType.IntegerAttribute);
md.AddNewField("salary", FieldType.KeyFigure);
md.AddNewField("salary_prev_year", FieldType.KeyFigure);
 
// define fields of salary table
var SalaryTableFields = TableFields.CreateEmptyTableFields(md);
SalaryTableFields.AddNewField("department");
SalaryTableFields.AddNewField("employee");
SalaryTableFields.AddNewField("year");
SalaryTableFields.AddNewField("salary");
 
// create salary table
var SalaryTable = MatrixTable.CreateTableWithElements_A(SalaryTableFields,
	"Acconting", "John", 2008, 4520.2233,
	"Acconting", "John", 2009, 4620.3333,
	"Acconting", "John", 2010, 4750.5533,
	"Acconting", "Mary", 2008, 5520.2233,
	"Acconting", "Mary", 2009, 5620.3333,
	"Acconting", "Mary", 2010, 5750.5533,
	"Sales", "John", 2008, 6520.2233,
	"Sales", "John", 2009, 6620.3333,
	"Sales", "John", 2010, 6750.5533,
	"Sales", "Hans", 2008, 6220.2233,
	"Sales", "Hans", 2009, 6520.3333,
	"Sales", "Hans", 2010, 6750.5533
	);
// view SalaryTable
MatrixTable.View_MatrixTable(SalaryTable, "Salary table");

Salary table

1. Round()

Round key figures of a table

/// <summary>
/// Round all key figures of table
/// </summary>
/// <param name="tbl">Input table</param>
/// <param name="DigitCount">Number of digits after decimal point after rounding</param>
/// <returns>Output table with rounded key figures</returns>
public static MatrixTable Round(MatrixTable tbl, int DigitCount)
{
	try
	{
		return MatrixTable.TransformKeyFigures(tbl, x => Math.Round(x, DigitCount));
	}
	catch (Exception ex)
	{
		throw new Exception("MatrixTable.Round: \n" + ex.Message);
	}
}
 
/// <summary>
/// Round selected key figure of table
/// </summary>
/// <param name="tbl">Input table</param>
/// <param name="DigitCount">Number of digits after decimal point after rounding</param>
/// <param name="KeyFigure">Name of key figure to be rounded</param>
/// <returns>Output table with rounded key figures</returns>
public static MatrixTable Round(MatrixTable tbl, int DigitCount, string KeyFigure)
{
	try
	{
		return MatrixTable.TransformKeyFigures(tbl, x => Math.Round(x, DigitCount), KeyFigure, KeyFigure);
	}
	catch (Exception ex)
	{
		throw new Exception("MatrixTable.Round: \n" + ex.Message);
	}
}

Note that the table function Round() calls the more general (and abstract) function TransformKeyFigures(). No need to write the code from scratch; you can use the general-purpose methods of the library.

Usage:

// round sales to two digits after decimal point
SalaryTable = MatrixTable.Round(SalaryTable, 2);
 
// view SalaryTable with rounded key figures
MatrixTable.View_MatrixTable(SalaryTable, "SalaryTable with rounded figures");

Salary table with rounded figures

2. AggregateAttribute()

Aggregate key figures of a table over given attribute

/// <summary>
/// Aggregate all key figures of table over given attribute.
/// Applies the same aggregation method on all key figures.
/// </summary>
/// <param name="tbl">Input table</param>
/// <param name="AttributeField">Name of attribute (numeric or text) to be excluded from table</param>
/// <param name="AggrOpt">(sum, min, max or avg)</param>
/// <returns>Aggregated table</returns>
public static MatrixTable AggregateAttribute(MatrixTable tbl, string AttributeField,
	AggregateOption AggrOpt = AggregateOption.nSum)
{
	// PARAMETER CHECKS
	if (tbl == null || tbl.IsEmpty)
		throw new Exception("MatrixTable.AggregateAttribute: Null or empty input table!\n");
 
	if (AttributeField == null || AttributeField == "")
		throw new Exception("MatrixTable.AggregateAttribute: Null or empty string AttributeField!\n");
 
	// check if AttributeField is an attribute in table
	FieldType ftype = tbl.GetFieldType(AttributeField);
 
	if (ftype != FieldType.DateAttribute && ftype != FieldType.IntegerAttribute && ftype != FieldType.TextAttribute)
		throw new Exception("MatrixTable.AggregateAttribute: Given AttributeField is not an attribute of input table!\n");
 
	// check if table has at least one key figure to aggregate
	if (tbl.KeyFigureFields.IsEmpty)
		throw new Exception("MatrixTable.AggregateAttribute: Input table must have at least one key figure to aggregate!\n");
 
	// parameter checks OK, continue ...
	try
	{
		// exclude field AttributeField from table
		MatrixTable TblOut = MatrixTable.ExcludeColumns(tbl, 
			TextVector.CreateVectorWithElements(AttributeField));
 
		// aggregate all key figures
		return MatrixTable.AggregateAllKeyFigures(TblOut, null, AggrOpt);
	}
	catch (Exception ex)
	{
		throw new Exception("MatrixTable.AggregateAttribute: \n" + ex.Message);
	}
}

Notice the elaborate parameter checks in this example. These checks must ensure the validity of input parameters, and produce understandable exception messages for users.

Usage:

// aggregate salary over years
var AggregatedSalaryTable = MatrixTable.AggregateAttribute(SalaryTable, "year");
 
// view SalaryTable with salary aggregated over years
MatrixTable.View_MatrixTable(AggregatedSalaryTable, 
	"SalaryTable with aggregated salaries over years");

Salary table with aggregated salaries over the attribute year

3. InsertFieldValueFromAnotherRow()

Insert field values from preceding or following rows

/// <summary>
/// Insert a new field and value to each row from a preceding or following row depending on RowOffset. 
/// A simple version SQL-based analytical functions like LEAD-LAG 
/// </summary>
/// <param name="InputTbl">Input table</param>
/// <param name="FieldName1">Field contained by input table whose preceding or following values are to be inserted</param>
/// <param name="FieldName2">New field to be inserted with values from preceding or following values rows</param>
/// <param name="RowOffset">Row offset: 1 means next row, -2 means previous of previous row, 0 means current row.</param>
/// <returns>Table with new field FieldName2 and values</returns>
public static MatrixTable InsertFieldValueFromAnotherRow(MatrixTable InputTbl,
	string FieldName1, string FieldName2, int RowOffset)
{
 
	#region "CHECK INPUT PARAMETERS"
	if (InputTbl == null || InputTbl.IsEmpty)    // IsEmpty means table has no fields
		throw new Exception("MatrixTable.InsertFieldValueFromAnotherRow: Null or empty input table!\n");
 
	MetaData md = InputTbl.metaData;
 
	// check if FieldName1 exists in input table
	if (InputTbl.GetFieldType(FieldName1) == FieldType.Undefined)
		throw new Exception("MatrixTable.InsertFieldValueFromAnotherRow: Field " + FieldName1 + " must exist input table!\n");
 
	// check if FieldName2 is defined in MetaData
	if (!MetaData.IfFieldExists(md, FieldName2))
		throw new Exception("MatrixTable.InsertFieldValueFromAnotherRow: Field " + FieldName2 + " is not defined in MetaData!\n");
 
	// check if FieldName2 exists in input table
	if (InputTbl.GetFieldType(FieldName2) != FieldType.Undefined)
		throw new Exception("MatrixTable.InsertFieldValueFromAnotherRow: Field " + FieldName2 + " must NOT exist input table!\n");
 
	// check field types
	if (md.GetFieldType(FieldName1) != md.GetFieldType(FieldName2))
		throw new Exception("MatrixTable.InsertFieldValueFromAnotherRow: Field1 and Field2 must have the same field types!\n");
 
	#endregion "CHECK INPUT PARAMETERS"
 
	// parameter checks done, continue ...
 
	// insert new field FieldName2 into InputTbl
	MatrixTable InputTblAdj = MatrixTable.InsertNewColumn(InputTbl, FieldName2);
 
	// create output table containing FieldName2
	var OutputTbl = MatrixTable.CreateEmptyTable(InputTblAdj.tableFields);
 
	// iterate over rows of input table
	int RowCount = InputTblAdj.RowCount;
	TableRow trow1, trow2;
 
	for (int i = 0; i < RowCount; i++)
	{
		trow1 = InputTblAdj.GetTableRow(i);
 
		if ((i + RowOffset) >= 0 && (i + RowOffset) <= (RowCount - 1))
		{
			trow2 = InputTblAdj.GetTableRow(i + RowOffset);
			TableRow.SetFieldValue(trow1, FieldName2, TableRow.GetFieldValue(trow2, FieldName1));
			OutputTbl = MatrixTable.AddRowToTable(OutputTbl, trow1);
		}
	}
	return OutputTbl;
}

Usage:

// insert salary from previous year into salary table
SalaryTable = MatrixTable.InsertFieldValueFromAnotherRow(SalaryTable,
	"salary", "salary_prev_year", -1);
 
// view SalaryTable with salary from previous year
MatrixTable.View_MatrixTable(SalaryTable,
	"SalaryTable with salary from previous year");

Salary table with current and previous year's salary in the same row

Copyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu

Leave a Reply