Sorting nested and concatenated tables

This example script is based on the Museum sample data set. See Running the sample table scripts for more information.

When you sort a table that contains concatenation in the dimension in which you are sorting, the elements within each variable are sorted separately. To illustrate how it works, we will sort the rows of a table that has two variables concatenated on both the side and the top axes. To do this, we need to specify the column on which we want to sort the rows:

With TableDoc.Tables
.AddNew("Concatenated", "age + gender * biology + before", "Sorting a concatenated table")
.Concatenated.SortColumn = "before{Base}"
End With

Here is the table, with the sort column highlighted:

Notice that the rows that relate to the Age variable have been sorted separately from the rows that relate to the Gender variable.

If there is nesting in the dimension of the table in which the sort row or column is located, you specify the sort row or column using the following syntax:

To illustrate this, we will create a table with the Before variable nested within the Biology variable on the top axis, and sort the rows of the table on the Base element of the Before variable that is nested within the Yes element of the Biology variable:

With TableDoc.Tables
  .AddNew("Nested1", "age * biology{.., ^Not_answered}
     > before{.., ^Not_answered}", "Sorting on a nested element")
  .Nested1.SortColumn = "biology{Yes} > before{Base}"
End With

Here is the table, with the sort column highlighted:

Notice that the sort order would be different if we had sorted the rows on the Base element of the Before variable that is nested within the No element of the Biology variable.

If you do not specify an element at an inner nesting level, the first element of the nested axis will automatically be used. For example, if we had merely specified an element of the outer variable for the sort column for the above table, the first element of the inner axis would be used. In this example the Base element is the first element of the Before variable, so in fact the following sort column specification would give exactly the same results:

When you sort a table that contains nesting in the dimension in which you are sorting, the elements within the inner nested variables are sorted. However, the elements of the outermost variable are not sorted. For example:

With TableDoc.Tables
.AddNew("Nested2", "interview > education{.., ^Not_answered} > before{.., ^Not_answered} * gender", "Sorting a nested dimension"
.Nested2.SortColumn = "Gender{Base}
End With

Here is the table, with the sort column highlighted:

Axes are not sorted if they contain a combination of nesting and concatenation and the Table Object Model does not issue a message to alert you to this.