- Filtering with condition table
- Filtering with condition cell
- 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.
Examples:
// 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"); |
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"); |
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"); |
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).
The figure below shows how a condition cell is applied on the rows of a 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"); |
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"); |
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"); |
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"); |
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.
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"); |
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
Copyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu
