Setting the population level
Sample script file: HierarchicalData.mrs
This example script is based on the Household sample data set. See
Running the sample table scripts for information on running the example scripts.
In
Understanding population levels, we looked at the implication of populating tables at different levels in a hierarchical data set. This topic explains more about the default population level, and how to change the population level and get a list of the valid population levels.
Setting the level
You set the population level for a table by setting the Table.Level property to the name of the required level. The name of the top level is always HDATA and the names of the lower levels are the same as the full names of the corresponding loops (Array objects). For example:
TableDoc.Table1.Level = "person"
TableDoc.Table2.Level = "person[..].trip"
If you specify a level that is not valid for the table, you will get an error. However, you can check the available levels as shown below.
Note You can show the population level as an annotation on the table. For example, the following code creates a right header annotation that shows the population level:
TableDoc.Default.Annotations[3].Specification = "Population level: {Level}"
Note that the {Level} macro always inserts the text “Top” for the top level and the {Level \l} macro is shown on the table by default. See
Annotations for more information.
Default level
This is the level at which a table is populated when you do not explicitly specify the population level. The default level depends on the levels of all of the variables that are in the table (including any numeric variables included in the cell contents) and the level of any filters:
▪If the filters and all of the variables are at the same level, the default level is the level of the variables.
▪If all of the filters and variables are at levels that are direct descendents of each other, the default level is the level of the lowest-level filter or variable.
▪If some or all of the filters or variables are at parallel levels (levels that are not direct descendents of each other), the default level is the first common parent level. (For an example, see “8. Tabulating variables from different "parallel" levels” in
Understanding population levels.)
Note that the default level is slightly different when a grid or loop slice is being used. See
Grid and loop slices for more information.
To illustrate this, let's look at the code used to create the tables in
Understanding population levels:
With TableDoc.Tables
.AddNew("Table1", "housetype * region", "Type of accommodation by Region - Household level")
.AddNew("Table2", "person.occupation * person.gender", "Occupation by Gender - Person level")
.AddNew("Table3", "person.occupation * person.gender", "Occupation by Gender - Household level")
.Table3.Level = "HDATA"
.AddNew("Table4", "person.trip.country * person.trip.purpose", "Countries visited by Purpose of trip - Trip level")
.AddNew("Table5", "person.trip.country * person.trip.purpose", "Countries visited by Purpose of trip - Person level")
.Table5.Level = "person"
.AddNew("Table6", "person.gender * person.trip.purpose", "Gender by Purpose of overseas trip - Trip level")
.AddNew("Table7", "person.gender * person.trip.purpose", "Gender by Purpose of overseas trip - Person level")
.Table7.Level = "person"
.AddNew("Table8", "vehicle.vehicletype * person.gender", "Vehicle type by person's gender")
.AddNew("Table9", "housetype * region", "Type of accommodation by Region - Person level")
.Table9.Level = "Person"
.AddNew("Table10", "Person.Trip.Purpose * Person.Gender", "Purpose of trip by Gender - Trip level")
.Table10.CellItems.AddNew(5, 0, "Person.Trip.DaysAway") ' Sum
.Table10.CellItems.AddNew(4, 0, "Person.Trip.DaysAway") ' Mean
.AddNew("Table11", "Person.Trip.Purpose * Person.Gender", "Purpose of trip by Gender - Person level")
.Table11.CellItems.AddNew(5, 0, "Person.Trip.DaysAway") ' Sum
.Table11.CellItems.AddNew(4, 0, "Person.Trip.DaysAway") ' Mean
.Table11.Level = "Person"
End With
Notice that the population level has been set for tables 3, 5, 7, 9, and 11. For the other tables, the default level has been used:
▪Table 1 has two top-level variables only, so the default level is the top (HDATA) level.
▪Table 2 has two person-level variables only, so the default level is the person level.
▪Table 4 has two trip-level variables only, so the default level is the trip level.
▪Tables 6 and 9 have one person-level variable and one trip-level variable. The trip level is a child of the person level, so the default level is the trip level.
▪Table 8 has one vehicle-level variable and one person-level variable. The vehicle level and the person level are on different branches of the hierarchy, so the default level is the top (HDATA) level, which is the only common ancestor level.
Available levels
You can check which levels are valid for a table using the Table.AvailableLevels property. The following example writes the valid levels to a text file:
Dim fso, txtfile
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.CreateTextFile("HierarchicalData.txt", True)
Dim MyTable
For Each MyTable in TableDoc.Tables
ListAvailableLevels(txtfile, MyTable.AvailableLevels, MyTable.Name)
Next
Sub ListAvailableLevels(TextFile, AvailableLevels, TableName)
Dim x
TextFile.WriteLine()
TextFile.WriteLine(TableName)
For x = LBound(AvailableLevels) To UBound(AvailableLevels)
TextFile.WriteLine(AvailableLevels[x])
Next
End Sub
Here is the output for our example tables:
Table1
HDATA
person
person[..].trip
person[..].tvdays
vehicle
vehicle[..].rating
Table2
HDATA
person
person[..].trip
person[..].tvdays
Table3
HDATA
person
person[..].trip
person[..].tvdays
Table4
HDATA
person
person[..].trip
Table5
HDATA
person
person[..].trip
Table6
HDATA
person
person[..].trip
Table7
HDATA
person
person[..].trip
Table8
HDATA
Table9
HDATA
person
person[..].trip
person[..].tvdays
vehicle
vehicle[..].rating
Table10
HDATA
person
person[..].trip
Table11
HDATA
person
person[..].trip
See also