How to Modify Tables via QScript – Q Help

This article describes how to net, merge, rename, move, and hide categories in a table, and update existing tables using a QScript.

Requirements

The importdata.zip file which includes the Phone data set that we will use for this article.
You have read How to Create a Custom QScript, How to Work with Variables via QScript, How to Add Folders and Text Outputs via QScript and How to Create Tables and R Calculations via QScript.
You are using one of the methods from How to Use QScripts in Q.

Method

Data Reduction

In Q, the categories in a table that appear as row and column headers are referred to as data reduction. This dataReduction property can access the built-in right-click and dragging behavior of combining, hiding, and moving table categories.

Let's look at an example where we wish to create a version of our Age question that will do the following:

Hide 15 and under and NET.
Merge the following:
- 20-24 yrs and 25-29 yrs as "20-29 yrs".
- 30-34 yrs and 35-44 yrs as "30-44 yrs".
- 45-54 yrs and 55-64 yrs as "45-64 yrs".
Create a net for our new categories of 20-29 yrs and 30-44 yrs called "NET 20-44 yrs".
Rename 65 and over as "65+ yrs".
Move our NET 20-44 yrs after 65+ yrs.

// Declare variables
var report = project.report;
var data_file = project.dataFiles[0];

// Duplicate variable
var age = data_file.getQuestionByName('Age');
var age_groups = age.duplicate('Age Groups');

// Modify table properties
age_groups.dataReduction.hide("15 and under");
age_groups.dataReduction.hide("NET");
age_groups.dataReduction.merge(["20-24 yrs", "25-29 yrs"], "20-29 yrs");
age_groups.dataReduction.merge(["30-34 yrs", "35-44 yrs"], "30-44 yrs");
age_groups.dataReduction.merge(["45-54 yrs", "55-64 yrs"], "45-64 yrs");
age_groups.dataReduction.createNET(["20-29 yrs", "30-44 yrs"], "NET 20-44 yrs");
age_groups.dataReduction.rename("65 and over", "65+ yrs");
age_groups.dataReduction.moveAfter("NET 20-44 yrs", "65+ yrs");

// Add table
var t = report.appendTable();
t.primary = data_file.getQuestionByName('Age Groups');

We begin by declaring our project's Report tree as report and our data file as data_file.
Next, we get the Age question and use the duplicate function to make a copy which we have called Age Groups. This is then declared as age_groups so we can use it for setting the various table properties, including name.
Each of the dataReduction properties take various arguments:
- hide - this simply requires the category label to hide.
- merge - this requires an array of labels to combine in square brackets and the new label.
- createNET - this requires an array of labels to net in square brackets and the new label.
- rename - this requires the existing label to rename and the new label.
- moveAfter - this requires the label to move and the label of the category you want to add it after.
Finally, we append a table directly to our report of our new variable Age groups.

Updating tables

A second common use case for modifying tables is when you simply wish to update existing parameters, such as statistics and row or column questions.

As an example, we will update all our tables with Gender in the columns and the p-value also showing:

includeWeb('QScript Selection Functions');
var data_file = project.dataFiles[0];
// Get all tables
var tables = [];
recursiveGetAllTablesInGroup(project.report, tables);

// Loop through each table
for (var j = 0; j < tables.length; j++) {
    // Add 'Gender' to the columns
    tables[j].secondary = data_file.getQuestionByName('Gender');
    // Add p value to the tables
    var cell_stats = tables[j].cellStatistics;
    cell_stats.push("p");
    tables[j].cellStatistics = cell_stats;
    // Update table name
    tables[j].name = tables[j].name + " with p value";
}

We use the recursiveGetAllTablesInGroup function from the QScript Selection Functions library to return all the tables in project.report, and place this in the empty tables array we declared above that. We can restrict this function to limit what items are included by using the subItems property, such as project.report.subItems[1] for the second item within our report.
For each table in tables, we loop through using j as the index.
We set the columns of our table with Gender using the secondary property.
We then pull the current cell statistics using cellStatistics, add the p value by pushing it into our cell_stats array, and then push this new array back into each table's cellStatistics property.
Finally, we set the name of each table by appending "with p value" to the original name.