Professional > Table scripting > Hierarchical data > Combining grid slices
 
Combining grid slices
Sample script file: CombineGridSlices.mrs
This example script is based on the Museum sample data set. See Running the sample table scripts for information on running the example scripts.
Sometimes you may want to combine the results of two or more iterations of a grid or loop to display combined data for particular categories that you are interested in. For example, in the Museum sample data set the Order grid shows the order in which visitors to the museum went to see individual galleries.
The following example shows how to combine grid slices to create a new variable showing the numbers of visitors who went to see a particular gallery as one of their first three visited. This example is available in a sample mrScriptBasic file called CombineGridSlices.mrs.
The script first displays the basic grid table as shown above:
.AddNew("Table1", "order * order.Column", "Order of galleries visited")
You can also tabulate each iteration of the grid independently. The script tabulates the first three slices in separate tables:
.AddNew("Table2", "* order[{First}].Column", "The first gallery visited")
.AddNew("Table3", "* order[{Second}].Column", "The second gallery visited")
.AddNew("Table4", "* order[{Third}].Column", "The third gallery visited")
These are the resulting tables:
Table with slice showing galleries visited first
Table with slice showing galleries visited second
Table with slice showing galleries visited third
The script then defines a function, CombineSlices, which creates a new variable that combines the results from the First, Second, and Third grid slices:
Function CombineSlices(MdmDoc, VariableName, Iterations, _
NewSliceName, NewSliceLabel)
The parameters of the function specify the metadata document, the existing variable that the new variable derives from, the grid slices to combine, a name for the new variable, and a label. For example, to combine the First, Second, and Third grid slices, the parameters have the values:
CombineSlices(MdmDoc, "order.Column", "{First, Second, Third}", _
"order_Initial", "One of first three galleries visited")
The function creates the new order_Initial variable as a derived categorical variable:
Set NewSlice = MdmDoc.CreateVariable(NewSliceName, NewSliceLabel)
NewSlice.DataType = 3 ' 3 = mtCategorical
NewSlice.SourceType = 4 ' 4 = sExpressions
The function then creates elements for the new variable by copying each element in the original Order.Column variable:
For Each Element In Variable.Elements
  Set NewElement = MdmDoc.CloneObject(Element)   NewSlice.Elements.Add(NewElement)
  Expr = "SUM(" + VarLevel.FullName + ".(" + _
      Variable.Name + " >= {" + NewElement.Name + "}" + _
      "AND LevelId.ContainsAny(" + Iterations + "))) > 0"
  NewSlice.Expressions.Add(NewElement.Name, Expr)
Next
The function also adds an expression for each element. The expression determines whether or not to include a respondent in the count for that element.
For Each Element In Variable.Elements
  Set NewElement = MdmDoc.CloneObject(Element)
  NewSlice.Elements.Add(NewElement)
   Expr = "SUM(" + VarLevel.FullName + ".(" + _
      Variable.Name + " >= {" + NewElement.Name + "}" + _
      "AND LevelId.ContainsAny(" + Iterations + "))) > 0"     NewSlice.Expressions.Add(NewElement.Name, Expr)
Next
For example, in the case of the Dinosaurs element, the expression would be:
"SUM(Order.(Column >= {Dinosaurs} AND LevelId.ContainsAny({First, Second, Third}))) > 0"
Finally, the new variable is added as a field in the metadata:
MdmDoc.Fields.Add(NewSlice)
For each iteration of the Order loop the central part of the expression determines if that iteration was First, Second, or Third and if the response was Dinosaurs. The SUM() > 0 test checks to see if at least one iteration of the loop meets this condition.
The following table shows the responses for selected respondents and the result of the expression for the Dinosaurs element.
Respondent
Grid slice
Gallery
Result of expression
307
{First}
{Birds}
 
 
{Second}
{Human_biology}
False
 
{Third}
{insects}
 
 
{Fourth}
{other}
 
308
{First}
{Dinosaurs}
 
 
{Second}
{other}
True
 
{Third}
{Conservation}
 
 
{Fourth}
{Birds}
 
309
{First}
{Birds}
 
 
{Second}
{Human_biology}
True
 
{Third}
{Dinosaurs}
 
 
{Fourth}
{Evolution}
 
...
 
 
 
314
{First}
{Human_biology}
 
 
{Second}
{Fossils}
False
 
{Third}
{other}
 
 
{Fourth}
{Dinosaurs}
 
When the table is populated, the UNICOM Intelligence Reporter - Survey Tabulation Aggregator counts the number of respondents where the element expression is true, and the total is displayed for the Dinosaurs element in the table.
This is the table produced by the script:
Note The base in the final table includes only the numbers of respondents who answered the question for the three iterations, whereas the base in the other tables includes all those who were asked the question, whether or not they responded.
See also
Hierarchical data