Creating test tables by combinations

The functions below can be used to generate test tables easily by generating combinations of given rows or value sets.

The first table function in the list below (CombinateFieldValues_B) can be used to generate tables with independent fields. The second function (CombinateTableRows) is the more general and versatile one; it can be used to generate tables with independent and dependent fields. The rest of the functions (3-5) are rather for more specific occasions; they won’t be required in most of the cases.

  1. CombinateFieldValues_B (see related article)
  2. CombinateTableRows (see related article)
  3. CombinateMatrixRows_A()
  4. CombinateMatrixRows_B()
  5. CombinateFieldValues_A()

Before creating any tables, all fields must be defined centrally within MetaData. Following MetaData object (md) will be used for all the examples below for creating tables with a subset of fields defined in MetaData.

// Define all fields in MetaData
var md = MetaData.CreateEmptyMasterData();
MetaData.AddNewField(md, "country", FieldType.TextAttribute);
MetaData.AddNewField(md, "car", FieldType.TextAttribute);
MetaData.AddNewField(md, "year", FieldType.IntegerAttribute);
MetaData.AddNewField(md, "month", FieldType.IntegerAttribute);
MetaData.AddNewField(md, "sales_eur", FieldType.KeyFigure);
MetaData.AddNewField(md, "costs_eur", FieldType.KeyFigure);


CombinateMatrixRows_A(TableFields TFields, TextMatrix TextAttributeVal, NumMatrix NumAttributeVal, KeyMatrix KeyFigVal)
Generate table by combinating rows of value matricesThis function generates a table with all possible row combinations of value matrices. Each value matrix can have different number of rows. Columns of value matrices must match the order of fields; first text attributes, then numeric attributes, then key figures. Empty value matrices must be entered for field types that have no fields.

// Define table structure
var tfields = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(tfields, "country");
TableFields.AddNewField(tfields, "car");
TableFields.AddNewField(tfields, "year");
TableFields.AddNewField(tfields, "month");
TableFields.AddNewField(tfields, "sales_eur");
 
// create value matrices
// 3x2 text matrix
TextMatrix tM =
TextMatrix.CreateMatrixWithElements(3, 2,
    "USA", "Mercedes",
    "USA", "Toyota",
    "Germany", "BMW");
// 3x2 numeric matrix
NumMatrix nM = NumMatrix.CreateMatrixWithElements(3, 2,
    2010, 3,
    2010, 6,
    2011, 9);
// 2x1 key figure matrix
KeyMatrix kM = KeyMatrix.CreateMatrixWithElements(2, 1, 120.0, 250.0);
// create table with all possible row combinations of value matrices
MatrixTable table1 = MatrixTable.CombinateMatrixRows_A(tfields, tM, nM, kM);
// display table in immediate window
System.Diagnostics.Debug.WriteLine("table1 = \n" + table1);
// view table
MatrixTable.View_MatrixTable(table1, "Table: sales_eur(country, car, year, month)");

Table: sales_eur(country, car, year, month)

Export table to excel fileValue matrices have 3, 3 and 2 rows for field types text, numeric and key figure in the example above. Accordingly, the generated table has 3x3x2 = 18 rows.

This table can easily be exported to an excel file as follows:

// export table to excel file
MatrixTable.Export_MatrixTable_To_Excel(table1);

Generate combination table without a key figureAs mentioned above, an empty matrix must be entered for field types that have no corresponding fields. In the following example there is no key figure. Therefore, an empty value matrix is entered for key figures.

// Define table structure</span>
var tfields2 = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(tfields2, "country");
TableFields.AddNewField(tfields2, "car");
TableFields.AddNewField(tfields2, "year");
TableFields.AddNewField(tfields2, "month");
// empty matrix for key figures
kM = KeyMatrix.CreateEmptyMatrix();
// create table with  row combinations
MatrixTable table2 = 
MatrixTable.CombinateMatrixRows_A(tfields2, tM, nM, kM);
// display table in immediate window
System.Diagnostics.Debug.WriteLine("table2 = \n" + table2);
// view table
MatrixTable.View_MatrixTable(table2, "Table without key figure");

Table without key figure

CombinateMatrixRows_B(TableFields TFields, TextMatrix TextAttributeVal,
NumMatrix NumAttributeVal,
KeyValueRange DefaultRangeForAllKeyFigures,
Dictionary RangeForSelectedKeywords = null,
int RandomSeed = 100)
Generate combination table with key figures within given rangesLike its version A above (CombinateMatrixRows_A) this function generates a table with all possible row combinations of value matrices with the difference that key figures are generated randomly within given ranges.

The input parameter RangeForSelectedKeywords of type Dictionary contains specific ranges for selected key figures. Dictionary is a collection type (structured array) in C# often used as a sort of associative array.

Other input parameter DefaultRangeForAllKeyFigures contains a single range which is valid for all key figures that have no specific ranges explicity indicated with RangeForSelectedKeywords. In other words, a KeyFigure-specific range overwrites the general default range. See the example below:

// Define table structure with two key figures
var tfields3 = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(tfields3, "country");
TableFields.AddNewField(tfields3, "car");
TableFields.AddNewField(tfields3, "year");
TableFields.AddNewField(tfields3, "month");
TableFields.AddNewField(tfields3, "sales_eur");
TableFields.AddNewField(tfields3, "costs_eur");
// define default range for all key figures
KeyValueRange DefaultRange = KeyValueRange.CreateRange(100.0, 500.0);
// define specific range for key figure "costs_eur"
var SpecificRange = new Dictionary<string, KeyValueRange>();
SpecificRange["costs_eur"] = KeyValueRange.CreateRange(60.0, 90.0);
// create table with two key figures
MatrixTable table3 = MatrixTable.CombinateMatrixRows_B(tfields3, tM, nM, DefaultRange, SpecificRange);
// display table in immediate window
System.Diagnostics.Debug.WriteLine("table3 = \n" + table3);
// view table
MatrixTable.View_MatrixTable(table3, "Table with two key figures, before rounding");

Table with two key figures, before rounding

// round all key figures to 1 digits after point
table3 = MatrixTable.TransformKeyFigures(table3, x => Math.Round(x, 1), null, null);
System.Diagnostics.Debug.WriteLine("round(1) table3 = \n" + table3);
// view table
MatrixTable.View_MatrixTable(table3, "Table with two key figures, after rounding");

Table with two key figures, after rounding

CombinateFieldValues_A(TableFields TFields,
Dictionary TextAttribValues,
Dictionary NumAttribValues,
Dictionary KeyFigValues,
string DefaultTextVal = “EMPTY”, int DefaultNumVal = 0,
double DefaultKeyFigVal = 0)
Generate table with field value combinationsThis function generates a table with all possible combination of given field values. A value set can be entered for each field. If a value set is not given for a field, a single default value is assumed as the value set.

The input parameters TextAttribValues, NumAttribValues and KeyFigValues are dictionaries (i.e. associative arrays, search for collection type Dictionary in C#/.NET) that contain value sets for each field. See the example below. Note that no value set is given for text attribute country. As a result, this field assumes the default value “EMPTY” in resultant table.

// Define table structure
var tfields4 = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(tfields4, "country");
TableFields.AddNewField(tfields4, "car");
TableFields.AddNewField(tfields4, "year");
TableFields.AddNewField(tfields4, "month");
TableFields.AddNewField(tfields4, "sales_eur");
// define value sets for text attributes
var TextAttribValues = new Dictionary<string, TextVector>();
TextAttribValues["car"] = TextVector.CreateVectorWithElements("BMW", "Audi", "Toyota");
// define value sets for numeric attributes
var NumAttribValues = new Dictionary<string, NumVector>();
NumAttribValues["year"] = NumVector.CreateVectorWithElements(2010, 2011);
NumAttribValues["month"] = NumVector.CreateVectorWithElements(6);
// define value sets for key figures
var KeyFigValues = new Dictionary<string, KeyVector>();
KeyFigValues["sales_eur"] = KeyVector.CreateVectorWithElements(20.50, 25.20);
// create table with  combinations of field values
var table4 = 
	MatrixTable.CombinateFieldValues_A(tfields4, TextAttribValues, NumAttribValues,
	KeyFigValues, DefaultTextVal: "EMPTY", DefaultNumVal: 0, DefaultKeyFigVal: 0.0);
// display table in immediate window
System.Diagnostics.Debug.WriteLine("table4 = \n" + table4);
// view table
MatrixTable.View_MatrixTable(table4, "Table: sales_eur(country, car, year, month), field combinations");

Table: sales_eur(country, car, year, month), field combinations

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

Leave a Reply