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, sort the rows of a table that has two variables concatenated on both the side and the top axes. To do this, you need to specify the column on which 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:

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:

The following example creates a table with the Before variable nested in 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:

The sort order would be different if you 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 you merely specified an element of the outer variable for the sort column, 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 the following sort column specification would give 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.