How to Create Tables and R Calculations via 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 Modify Variables and Value Attributes via QScript and How to Add Folders and Text Outputs via QScript.
• You are using one of the methods from How to Use QScripts in Q.
• This assumes you have also performed the below steps from How to Modify Variables and Value Attributes via QScript:
  • Set a regrouped variable set called Q5 as per the steps in the Combine section.
  • Set Q5 as a set of filters as per the steps in the Variable and question properties section.
  • Set Gender as a weight as per the steps in the Variable and question properties section.

Method

In Q, the project's Report tree is referenced as the project.report object. You can append various objects to your Report tree, such as folders, tables, R calculations, and visualizations.

Here, we will focus on tables and R calculations.

Summary tables and crosstabs

The general workflow for creating a table via QScript is to either first add a folder and then insert a table on it, or else add a table directly to the report. In this example, we will create a summary percentage table based on our Age variable and add it to a new folder:

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

// Add folder
var folder = report.appendGroup();
folder.name = "Demographics";

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

• We begin by declaring our project as report and data file as data_file so they can be used later.
• We then get our Age question object from our data set using getQuestionByName.
• Next, we append a folder to report using appendGroup with the name Demographics.
• Finally, we use the appendTable function to insert a summary table called t on our page by specifying the primary question for the rows. By default the secondary or columns question will be set to SUMMARY or RAW DATA depending on the data format.

Note, to do the same without adding a folder, we could write the following:

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

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

The main difference here is that appendTable is appended to report.

Further arguments can be added to our example. Let's add Work status in the columns to create a crossbreak and apply the Optus (Q5_4) filter we previously created in How to Modify Variables and Value Attributes via QScript:

t.secondary = data_file.getQuestionByName('Work status');
t.filters = [data_file.getVariableByName('Q5_4')];

Note, that filters are stored in an array (represented by square brackets) and are referenced by variable name. In order to use filters with multiple filter variables that are checked as F in the Variables and Questions tab's Tag column, such as both Q5_4 (Optus) and Q5_6 (Telstra), we would do the below:

t.filters = [data_file.getVariableByName('Q5_4'), data_file.getVariableByName('Q5_6')];

Adding a weight, on the other hand, is simply a matter of referencing a variable by name that is checked as W in the Variables and Questions tab's Tag column:

t.weight = data_file.getVariableByName('q35');

Table statistics

Q differentiates between statistics in cells, to the right and below a table. You can see these by right-clicking any table > Statistics - Cells/Right/Below.

In QScript theses are referenced as below:

• cellStatistics - statistics shown in the cells of a table.
• rowStatistics - statistics shown to the right of each table row.
• columnStatistics - statistics shown below a table.

One thing to note here is that these properties use the original statistic name. 

In the below example, we set the table statistic as Column n as an array:

t.columnStatistics = ["Column n"];

Calculations

Appending R Calculations is just as easy as adding a table except we use the appendR function. For example, we could add a calculation to our Demographics folder by using the referenceName of our table or t:

folder.appendR(t.referenceName);

If we wanted to sum all rows for each column in this crosstab, we could instead include our table reference name in a formula:

folder.appendR("table.average = rowSums("+t.referenceName+", na.rm=T)");

This would then paste the following R code in our calculation:

table.average = rowSums(table.Age.by.Work.status, na.rm=T)

Next

How to Create a Chart via QScript

Later

How to Modify Tables via QScript

See Also

How to Work with Variables via QScript

How to Modify Variables and Value Attributes via QScript

How to Add Folders and Text Outputs via QScript

How to Create a Custom QScript

How to Use QScripts in Q

JavaScript Fundamentals