Creating test tables with finaquant® protos – 1

CombinateFieldValues_B()1 is one of the several table functions in finaquant® protos (free .net library) which can be used for generating test tables including any number of independent attributes.

public static MatrixTable CombinateFieldValues_B(TableFields TFields,
	Dictionary TextAttribValues, Dictionary NumAttribValues,
	KeyValueRange DefaultRangeForAllKeyFigures, Dictionary RangeForSelectedFigures = null,
	string DefaultTextVal = "EMPTY", int DefaultNumVal = 0, int RandomSeed = 100)

Following example shows how a test table (CostTable) with five independent attributes (category, product, brand, modelyear, date) and three random-valued key figures (costs, margin, sales) can be created in six steps.

Step 1: Define all fields centrally in MetaData

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

Step 2: Define fields of the test table

// define table structure
var CostTableFields = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(CostTableFields, "category");
TableFields.AddNewField(CostTableFields, "product");
TableFields.AddNewField(CostTableFields, "brand");
TableFields.AddNewField(CostTableFields, "modelyear");
TableFields.AddNewField(CostTableFields, "date");
TableFields.AddNewField(CostTableFields, "costs");
TableFields.AddNewField(CostTableFields, "margin");
TableFields.AddNewField(CostTableFields, "sales");

Step 3: Create vectors with attribute values that are to be combinated

// attribute values
// text attributes
TextVector CountryVal = TextVector.CreateVectorWithElements("Peru", "Paraguay", "Argentina", "Brasil");
TextVector CategoryVal = TextVector.CreateVectorWithElements("Economy", "Luxury", "Sports", "Family");
TextVector BrandVal = TextVector.CreateVectorWithElements("Toyota", "Honda", "BMW", "Audi");
TextVector ProductVal = TextVector.CreateVectorWithElements("Car", "Bus", "Motor");
// numeric attributes
NumVector ModelVal = NumVector.CreateVectorWithElements(2008, 2009, 2010);
NumVector DateVal = NumVector.CreateSequenceVector(
	StartValue: DateFunctions.DayToNumber(1, 1, 2010),
	Interval: 10, nLength: 5);

Step 4: Assign a value vector to each field of the test table

// initiate field value dictionaries
var TextAttribValues = new  Dictionary();
var NumAttribValues = new  Dictionary();
// assign a value vector to each field
TextAttribValues["country"] = CountryVal;
TextAttribValues["category"] = CategoryVal;
TextAttribValues["product"] = ProductVal;
TextAttribValues["brand"] = BrandVal;
NumAttribValues["modelyear"] = ModelVal;
NumAttribValues["date"] = DateVal;

Step 5: Define default and specific ranges for key figures

// default range for all key figures
KeyValueRange DefaultRangeForAllKeyFigures = KeyValueRange.CreateRange(5000, 10000);
// range for selected key figures
var RangeForSelectedKeywords = new Dictionary();
RangeForSelectedKeywords["margin"] = KeyValueRange.CreateRange(0.10, 0.60);

Step 6: Create test table

// create test table
CostTable = MatrixTable.CombinateFieldValues_B(CostTableFields,
	TextAttribValues, NumAttribValues, DefaultRangeForAllKeyFigures, RangeForSelectedKeywords);

Round all key figures in test table

Unrounded key figures look a bit ugly; let’s round them.

// round all key figures to 2 digits after decimal point
CostTable = MatrixTable.TransformKeyFigures(CostTable, x => Math.Round(x, 2),
	InputKeyFig: null, OutputKeyFig: null);

View test table

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

View test table created with finaquant protos

The 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:
CostTable_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