Filtering tables with finaquant® protos

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.
As explained below in detail, finaquant® protos (free .net library) offers several table functions for selecting or deselecting rows of tables1. Three methods are available for filtering tables:

  1. Filtering with condition table
  2. Filtering with condition cell
  3. Filtering with condition list (array of condition cells)

These filtering functions constitute the basis for powerful and versatile table transformation functions like the function router that map selected subtables to corresponding table functions to generate an array of results. In that sense, filtering functions are the foundation of rule-based calculations (rule engine); they determine, which subset of rows (i.e. subtables) are fed to which table functions with which set of parameters.

1) Filtering rows with a condition table

Rows of the input table to be filtered are matched to rows of a condition table with possible use of match-all (joker) values. Depending on the value of boolean (true or false) parameter ExcludeMatchedRows matched rows of input table are either selected (included), or deselected (excluded). All the attributes of the condition table must be a subset of the attributes of the input table.

Filtering rows with a condition table

Examples:

Input table

// create condition table TableFields CondTableFields = TableFields.CreateEmptyTableFields(md); 
TableFields.AddNewField(CondTableFields, "family");
TableFields.AddNewField(CondTableFields, "birth_year"); 
MatrixTable CondTable = MatrixTable.CreateTableWithElements_A(CondTableFields, 
	"mammals", 0, // all years 
	"ALL", 2005, // all families 
	"Fish", 2008 
	); 
// view table 
MatrixTable.View_MatrixTable(CondTable, "Filtering with condition table: Condition table");

Condition table

Case 1: Select (include) matched rows

// CASE 1: Include (select) mathched rows: ExcludeMatchedRows = false
FilteredTable = MatrixTable.FilterTableA(AnimalsTable, CondTable, ExcludeMatchedRows: false, 
	JokerMatchesAllvalues: true, TextJoker: "ALL", NumJoker: 0); 
// view resultant filtered table 
MatrixTable.View_MatrixTable(FilteredTable, "Filtering with condition table (include): Output table");

Filtered table 1 (with condition table)

Case 2: Deselect (exclude) matched rows

// CASE 2: Exclude (deselect) mathched rows: ExcludeMatchedRows = true
FilteredTable = MatrixTable.FilterTableA(AnimalsTable, CondTable, ExcludeMatchedRows: true, 
	JokerMatchesAllvalues: true, TextJoker: "ALL", NumJoker: 0); 
// view resultant filtered table 
MatrixTable.View_MatrixTable(FilteredTable, "Filtering with condition table (exclude): Output table");

Filtered table 2 (with condition table)

2) Filtering rows with a condition cell

Each cell defines a condition for a certain field of a table. A condition can be a value set like {“tiger”, “lion”} or a range like (2005, 2008] of four possible types: open-open (), open-closed (], closed-open [) and closed-closed [].

Cell logic (union, intersection, exclusion) plays no role for filtering with a single cell; it is only relevant for chained array of cells for combining the selection logic of multiple cells (see condition list below).

Condition cells for filtering tables

The figure below shows how a condition cell is applied on the rows of a table.
Applying Cell Condition on Table

Examples:

// create a condition cell 
string CellField = "animal"; 
ConditionLogic CondLogic = ConditionLogic.Intersect; // not relevant for this filter with single cell 
ConditionCell CondCell = ConditionCell.CreateConditionCell_A(md, CellField, "[camel, tiger)", CondLogic);

Case 1: Cell field animal in range “[camel, tiger)”, include rows

// CASE 1: animal in range [camel, tiger), ExcludeMatchedRows = false (include) 
// apply condition cell on table 
FilteredTable = MatrixTable.FilterTableB(AnimalsTable, CondCell, ExcludeMatchedRows: false, 
	IgnoreHierarchy: true, ErrorIfConditionNotRelevant: false); 
// view output table 
MatrixTable.View_MatrixTable(FilteredTable, "(1) Filtering with condition cell (include): OutputTable");

Filtering rows of table with condition cell, include

Note that all the rows with animals from “camel” (included) to “tiger” (excluded) in alphabetical order are included in the resultant filtered table.

Case 2: Cell field animal in range “[camel, tiger)”, exclude rows

// CASE 2: animal in range [camel, tiger), ExcludeMatchedRows = true (exclude)
// apply condition cell on table
FilteredTable = MatrixTable.FilterTableB(AnimalsTable, CondCell, ExcludeMatchedRows: true,
	IgnoreHierarchy: true, ErrorIfConditionNotRelevant: false);
// view output table
MatrixTable.View_MatrixTable(FilteredTable, "(2) Filtering with condition cell (exclude): OutputTable");

Filtering rows of table with condition cell, exclude rows

Hierarchical relations of attributes can also be used for defining conditions with cells. Let’s add a hierarchy table to MetaData to demonstrate this.

// add 1. hierarchy table to MetaData
TableFields Hierarchy1Fields = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(Hierarchy1Fields, "sex");
TableFields.AddNewField(Hierarchy1Fields, "name");
// create table
MatrixTable Hierarchy1Tbl = MatrixTable.CreateTableWithElements_A(Hierarchy1Fields,
	"male", "Shirkaan",
	"male", "Mufassa",
	"male", "Sinbad",
	"male", "Jacob",
	"female", "Zazoo",
	"female", "Marlin",
	"male", "Hassan"
	);
TextVector OrderedH1Fields = TextVector.CreateVectorWithElements("sex", "name");
MetaData.AddNewHierarchy(md, OrderedH1Fields, Hierarchy1Tbl);
// view hierarchy table
MatrixTable.View_MatrixTable(Hierarchy1Tbl, "Hierarchy table 1 in MetaData: sex & name");

Hierarchy table

We can now define a condition on sex even if the field sex is not directly included in the animal table.

Case 3: Sex in value set {“female”}, include rows, use hierarchical relationship between the fields sex and name

// CASE 3: Use hierarchy relation, female animals, ExcludeMatchedRows = false (include)
// create condition cell
CellField = "sex";
CondLogic = ConditionLogic.Intersect;   // not relevant for this filter with single cell
CondCell = ConditionCell.CreateConditionCell_A(md, CellField, "female", CondLogic);
// apply condition cell on table, IgnoreHierarchy = false
FilteredTable = MatrixTable.FilterTableB(AnimalsTable, CondCell, ExcludeMatchedRows: false,
	IgnoreHierarchy: false, ErrorIfConditionNotRelevant: false);
// view output table
MatrixTable.View_MatrixTable(FilteredTable, "(3) Filtering with condition cell (sex in {female}): OutputTable");

Filtering rows of table with condition cell, include

As expected, only the rows with female animals are selected.

3) Condition list: Chained condition cells

Individual condition cells can be combined to formulate more complex conditions on multiple fields. The cell logic property of a cell determines how it affects the overall filter logic. The outcomes of condition cells (i.e. selected rows) are combined with the corresponding set operation of the cell in order (union, intersection or exclusion), from left to right.

Note that each condition cell in the list is directly applied on the original input table; not on the outcome of the preceding cell. The set operation defined by the cell logic is applied on the outcome of the preceding cell.

Condition list as chained condition cells

Example: Filtering with a condition list containing four cells

// initiate a condition list
ConditionList CondList = ConditionList.CreateEmptyList();
// Add 1. cell to list
CellField = "family";
CondLogic = ConditionLogic.Intersect;
CondCell = ConditionCell.CreateConditionCell_A(md, CellField, "mammals, birds", CondLogic);
ConditionList.AddCellToList(CondList, CondCell);
// Add 2. cell to list
CellField = "animal";
CondLogic = ConditionLogic.Exclude;     // Set Subtraction
CondCell = ConditionCell.CreateConditionCell_A(md, CellField, "tiger", CondLogic);
ConditionList.AddCellToList(CondList, CondCell);
// Add 3. cell to list
CellField = "birth_year";
CondLogic = ConditionLogic.Include;     // UNION
CondCell = ConditionCell.CreateConditionCell_A(md, CellField, "[2007, 2008]", CondLogic);
ConditionList.AddCellToList(CondList, CondCell);
// Add 4. cell to list
CellField = "sex";
CondLogic = ConditionLogic.Exclude;
CondCell = ConditionCell.CreateConditionCell_A(md, CellField, "female", CondLogic);
ConditionList.AddCellToList(CondList, CondCell);
// Apply condition list to animal table, use hierarchical relations
FilteredTable = MatrixTable.FilterTableC(AnimalsTable, CondList, IgnoreHierarchy: false,
	ErrorIfConditionNotRelevant: true);
// view output table
MatrixTable.View_MatrixTable(FilteredTable, "Filtering with condition list");

Condition list: Filtered table

Notice that the resultant filtered table contains only rows with male animals because all the rows with female animals were excluded by the last cell of the condition list.
Written by: Tunç Ali Kütükçüoglu

Digiprove sealCopyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu
2. Three methods are available for filtering tables: Filtering" data-image="http://software.tuncalik.com/wp-content/uploads/2013/01/FilteringTables_CondTable.png" data-button=""> Share
  1. Table functions for filtering rows of tables are available in releases 1.02 and higher. []
  2. Table functions for filtering rows of tables are available in releases 1.02 and higher. []
This entry was posted in Calculation engine and tagged , . Bookmark the permalink.

Leave a Reply