Server User Guides > Survey Tabulation > Working with hierarchical data > Filtering hierarchical data
 
Filtering hierarchical data
Sample script file: HierarchicalFilters.mrs
This topic provides a number of examples to illustrate how filters and expressions work when you are using the hierarchical view of the data. The examples are based on the Household XML sample.
First, create an unfiltered table at the Person level using the following table specification:
Region{.., ^No_answer} * NumRooms > Person.Gender
This table contains two variables (Region and NumRooms) at the household (HDATA) level and one variable (Person.Gender) at the Person level. Here is the table:
Unfiltered table showing region by number of rooms
This graphic is described in the surrounding text.
Now add a filter to the table to select females only. To do this create a person-level filter, because the Gender variable is at the Person level. The fourth parameter of the Filters.AddNew method defines the level of the filter. For example:
TableDoc.MyTable.Filters.AddNew("Females", "Gender = {Female}", , "Person")
Table showing region by number of rooms, filtered to show females only
This graphic is described in the surrounding text.
Unlike the axis specification, in the filter expression the Gender variable has been specified as “Gender” and not using its full name of Person.Gender. This is because you must always specify the names of the variables in the filter expression relative to the level of the filter. In this example the filter is at the Person level and so you must use the variable’s name rather than its full name.
Now suppose you also want to filter the table on the household-level NumRooms variable. You can do this by adding another separate filter at the top level. The level parameter of the Filters.AddNew method defaults to the top (HDATA) level, so you do not need to set it explicitly. For example:
TableDoc.Tables.MyTable.Filters.AddNew("UpTo7Rooms", "NumRooms < 8")
Here is the table after applying both filters:
Table showing regions by number of rooms with local and global filters
This graphic is described in the surrounding text.
When you specify multiple separate filters like this, UNICOM Intelligence Professional automatically down-levs the expressions to the level of the lowest filter and combines the resulting expressions using the And operator.
When you create a filter, all of the variables you use in the filter expression must be at the level of the filter. However, you can include variables from a higher parent level by down-leving them. For example, you can create a single Person-level filter that is identical to the combined separate Person-level and Top-level filters you created above by down-leving the NumRooms variable to the Person level:
TableDoc.Tables.MyTable.Filters.AddNew _
    ("FemalesAndUpTo7RoomsAtPerson", _
    "Gender = {Female} And ^.NumRooms < 8", , "person")
The NumRooms variable has been preceded by the down-lev operator (^.). This filter selects women and girls who live in households that have less than eight rooms, just as the combination of the two separate filters does.
You can include variables from a lower child level by up-leving them. For example, if you wanted to create a filter at the household level, you could up-lev the Gender variable. However, it is not possible to create the previous filter at the household level. You can either select households that contain at least one female and have less than eight rooms:
TableDoc.Tables.MyTable.Filters.AddNew _
     ("HouseholdsWithFemalesAndUpTo7RoomsAtTop", _
    "Sum(Person.(Gender = {Female})) > 0 And NumRooms < 8")
Which gives this table:
This graphic is described in the surrounding text.
Or you can select households that contain no males and have less than eight rooms:
TableDoc.Tables.MyTable.Filters.AddNew _
    ("AllFemaleHouseholdsAndUpTo7RoomsAtTop", _
    "Sum(Person.(Gender = {Male})) = 0 And NumRooms < 8")
Which gives this table:
This graphic is described in the surrounding text.
However, it is not possible to create a household-level filter to select females only, because that information is not available when you up-lev the data to the household level. The reason for this is that the person-level data for each household is collapsed together.
You up-lev data by using the up-lev operator ( .( ) in combination with one of the aggregate functions supported by the UNICOM Intelligence Data Model.
You can create tables with filters that make use of loop slice expressions. When using a slice expression, the level of the slice expression is the level of the first identifier. In the following cases, the level is that of the Person (HDATA).
For a single filter that makes use of a loop slice expression:
TableDoc.Tables.MyTable.Filters.AddNew("FirstPersonIsMale","Person[1].Gender = {Male}", , "HDATA")
Which gives this table:
This graphic is described in the surrounding text.
For two filters filter that makes use of a loop slice expression:
TableDoc.Tables.MyTable.Filters.AddNew("FirstPersonIs", _
    "Person[1].Gender = {Male}", , "HDATA")
TableDoc.Tables.MyTable.Filters.AddNew("FirstPerson", _
    "person[1].tvdays[{Channel_1}].Column.ContainsAny({E12_days,
    E34_days})"
, , "HDATA")
Which gives this table:
This graphic is described in the surrounding text.
When creating multiple filters at different levels for individual tables, the filter levels must have a direct parent-child relationship with each other and not be parallel to each other (on different branches of the tree). For example, using the Household sample data, you cannot create separate filters at the Person and the Vehicle levels for the same table, because these levels are parallel to each other. However, you can create separate filters for the same table at the Person level and Trip levels, because the Trip level is a direct descendant of the Person level. This restriction applies regardless whether you define the filters as individual table filters or using global and default filters.
If you need to filter a table on variables from parallel levels, you must include the variables in the same filter and up-lev the variables from one of the levels to the first common ancestor level and then down-lev the data to the level of the filter. For example, the following Person-level filter includes the VehicleType variable from the parallel Vehicle-level:
TableDoc.Tables.MyTable.Filters.AddNew _
    ("MalesAndMotorBikesAtPerson", _
    "Gender = {Male} And ^.Sum(Vehicle.(VehicleType = _
    {Motorbike})) > 0", , "person")
See also
Working with hierarchical data