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"); |
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"); |
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 (1263 downloads)
Copyright secured by Digiprove © 2012 Tunc Ali Kütükcüoglu
