How to Modify Tables via QScript

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

Requirements

• A Displayr Enterprise user license which includes the ability to add custom analysis menu items or use the Displayr API.
• 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 Pages, Folders, and Headings via QScript, and How to Create Tables and Calculations via QScript.
• You are using one of the methods from How to Use QScripts in Displayr.

Method

Data Reduction

In Displayr 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 page
var page = report.appendGroup();
page.name = "Age";

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

• We begin by declaring our document's Pages section 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 add a page to our document called Age and append a table 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 variable (sets).

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;
    tables[j].IsOnPage = false;
}

• We use the recursiveGetAllTablesInGroup function from the QScript Selection Functions library to return all the tables in project.report, that is our whole document, 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 (i.e. page 2) within our document.
• 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 IsOnPage property of the tables to false so they now appear off the pages.

See Also

How to Work with Variables via QScript 

How to Add Pages, Folders, and Headings via QScript

How to Create Tables and Calculations via QScript

How to Create a Custom QScript

How to Use QScripts in Displayr

JavaScript Fundamentals

How to Work with JavaScript Arrays and Loops