Creating test tables with finaquant® protos – 2

All the C# code examples presented on this page can be found in the related demo function of MS Visual Studio project FinaquantProtosStarter which can be downloaded at the product page of the free .NET library finaquant® protos.

CombinateTableRows()1 is a table function in finaquant® protos (free .net library) which can be used for generating test tables including any number of dependent and independent attributes.

public static MatrixTable CombinateTableRows(params MatrixTable[] TableArray)

This table function creates test tables by combinating (i.e. cartesian multiplication) the rows of any number of input tables, provided that these tables have no common fields.

CombinateTableRows() is the more general and mightier alternative to CombinateFieldValues_B() which was introduced in the previous related article. With CombinateTableRows() it is possible to create test tables with dependent as well as independent fields.

Following example shows how a test table with 8 fields can be generated with dependent attribute pairs category-product, country-brand and date-year.

Step 1: Define all fields centrally in MetaData

// define metadata
MetaData md = MetaData.CreateEmptyMasterData();
MetaData.AddNewField(md, "country", FieldType.TextAttribute);
MetaData.AddNewField(md, "category", FieldType.TextAttribute);
MetaData.AddNewField(md, "brand", FieldType.TextAttribute);
MetaData.AddNewField(md, "product", FieldType.TextAttribute);
MetaData.AddNewField(md, "year", FieldType.IntegerAttribute);
MetaData.AddNewField(md, "date", FieldType.DateAttribute);
MetaData.AddNewField(md, "margin", FieldType.KeyFigure);
MetaData.AddNewField(md, "sales", FieldType.KeyFigure);

Step 2: Create input tables

// table 1
var Tbl1Fields = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(Tbl1Fields, "category");
TableFields.AddNewField(Tbl1Fields, "product");
TableFields.AddNewField(Tbl1Fields, "costs");
MatrixTable Tbl1 = MatrixTable.CreateTableWithElements_A(Tbl1Fields,
	"Office", "Notebook", 12.0,
	"Office", "Pencil", 5.0,
	"Office", "Printer", 120.0,
	"Household", "Washing machine", 450.0,
	"Household", "Vacuum cleaner", 250.0
	);
// table 2
var Tbl2Fields = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(Tbl2Fields, "country");
TableFields.AddNewField(Tbl2Fields, "brand");
MatrixTable Tbl2 = MatrixTable.CreateTableWithElements_A(Tbl2Fields,
	"Japan", "Canon",
	"USA", "HP"
	);
// table 3
var Tbl3Fields = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(Tbl3Fields, "date");
TableFields.AddNewField(Tbl3Fields, "year");
MatrixTable Tbl3 = MatrixTable.CreateTableWithElements_A(Tbl3Fields,
	DateFunctions.DayToNumber(1, 1, 2010), 2010,
	DateFunctions.DayToNumber(1, 2, 2010), 2010,
	DateFunctions.DayToNumber(1, 3, 2010), 2010
	);

Step 3: Generate test table

// generate test table as all possible row combinations of input tables
MatrixTable TestTable = MatrixTable.CombinateTableRows(Tbl1, Tbl2, Tbl3);
// view table
MatrixTable.View_MatrixTable(TestTable, "CombinateTableRows_C(): Test table");

Test table before adding key figure sales

If desired, random-valued key figures can be added to test table subsequently.

Step 4: Insert random valued new key figure “sales” into test table

// insert new key figure "sales" with random values within range 1200-2500
TestTable = MatrixTable.InsertNewColumn(TestTable, "sales", 0.0);
TestTable = MatrixTable.AssignRandomValues(TestTable, KeyFigure: "sales",
	LowerLimit: 1200, UpperLimit: 2500);
// round all key figures to 2 digits after decimal point
TestTable = MatrixTable.TransformKeyFigures(TestTable, x => Math.Round(x, 2),
	InputKeyFig: null, OutputKeyFig: null);
// view table
MatrixTable.View_MatrixTable(TestTable, "CombinateTableRows_C(): Test table after adding key figure sales");

Test table after adding key figure "sales"

This test table can be exported to an excel sheet by pressing the button “Export to Excel”. You can download this excel file for a closer check:
TestTable_FinaquantProtos.xls (December 2012)

Copyright secured by Digiprove © 2012 Tunc Ali Kütükcüoglu
  1. This table function is available in releases 1.02 and higher []
This entry was posted in Calculation engine and tagged , . Bookmark the permalink.

Leave a Reply