Filtering hierarchical data

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:

Notice that 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:

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:

Notice that, 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 we must use the variable's name rather than its full name.

Now suppose we also want to filter the table on the household-level NumRooms variable. We can achieve this by adding another separate filter at the top level. The level parameter of the Filters.AddNew method automatically defaults to the top (HDATA) level, so we do not need to set it explicitly. For example:

Here is the table after applying both filters:

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, we can create a single Person-level filter that is identical to the combined separate Person-level and Top-level filters we created above by down-leving the NumRooms variable to the Person level:

Notice that 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 we wanted to create our filter at the household level, we could up-lev the Gender variable. However, it is not possible to create the previous filter at the household level. We can either select households that contain at least one female and have less than eight rooms:

which gives this table:

Or we can select households that contain no males and have less than eight rooms:

which gives this table:

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.

Notice that 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:

which gives this table:

For two filters filter that makes use of a loop slice expression:

which gives this table:

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 descendent 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: