How to Modify Table Rows and Columns Using a Rule

Introduction

This article describes how to modify tables by adding, deleting, moving, and relabeling rows and columns.

Requirements

A table with a Rule applied.
This article assumes you are already in the Automate > Custom Rule or Edit Rule > Edit JavaScript Rule view specified in How to Use Rules in Q and How to Customize a Rule.
You have read How to Create a Custom Rule.
We'll use the below crosstab as an example:

Method

Table rows and columns are indexed in the same way as any JavaScript array. This means the first index starts at 0 rather than 1 and the last index is 1 less than the total number. Keeping this in mind, Q has a series of functions that allow users to modify these dimensions.

Adding rows and columns

There are 2 sets of functions based on the level of modifications you wish to make. To add a new row or column based on the physical dimensions of your table, you would use insertRowAfter and insertColumnAfter which require the index where you wish to add the row/column and the new label:

table.insertRowAfter(8,"New row"); // Add after ninth row
table.insertColumnAfter(1,"New column"); // Add after second column

If you have a table where the value attribute categories are displayed to the right as columns (instead of rows), that is a right_table (e.g. a Pick One - Multi table), the below will additionally add the applicable row as a column in this case:

insertRowAfterComplete(0, "New category"); // Add after first row (transposed as a column)

And if this table has had rows and columns switched so that the value attribute categories are now displayed in the rows as a below_table, the below will additionally add the applicable column as a row in this case:

insertColumnAfterComplete(0, "New category"); // Add after first column (transposed as a row)

Deleting rows and columns

To delete a row or column based on the physical dimensions of your table, you would use deleteRow and deleteColumn which require only the index of the row/column you wish to remove:

table.deleteRow(0); // Delete first row
table.deleteColumn(table.numberColumns-1); // Delete last column

The equivalent functions for applying this additionally to the right_table for rows and below_table for columns, would then be:

deleteRowComplete(0); // Delete first row (transposed as a column)
deleteColumnComplete(0); // Delete first column (transposed as a row)

To delete columns based on their label instead you can use code like the following (just change column to Row to do this for rows):

//name of columns to hide
var colnames=["Pepsi Max","Coke Zero"];

//load table functions
includeWeb("Table JavaScript Utility Functions");

//loop through the list of colnames and delete the column if found
for(var thecol = 0; thecol < colnames.length; thecol++) {
   //if label is found in column labels the index (position) is above -1
   if(table.columnIndex(colnames[thecol]) > -1)
      deleteColumnComplete(table.columnIndex(colnames[thecol]));
}

Moving rows and columns

To move a row or column based on the physical dimensions of your table, you would use these functions:

table.moveRowAfter(8,table.numberRows-1); // Move ninth row to last row
table.moveColumnAfter(2,table.numberColumns-1); // Move third column to last column

The equivalent functions for applying this additionally to the right_table for rows and below_table for columns, would then be:

moveRowAfterComplete(0, 2); // Move first row after second (transposed as a column)
moveColumnAfterComplete(0, 2); // Move first column after second (transposed as a row)

Relabeling rows and columns

When it comes to relabeling rows and columns the process is slightly different. We first need to return the appropriate labels by using table.rowLabels and table.columnLabels.

If we wanted to, for example, replace the last row label with New label, we would essentially need to update the last index of the labels array and then pass it back to the table as follows:

var labels = table.rowLabels; // Get all row labels
labels[table.numberRows-1] = "New label"; // Set last index label
table.rowLabels = labels; // Update table row labels

If we wanted to instead find and replace a label without specifying the index, we would use table.rowIndex(label) to return the index that matches the specified label, i.e. New row:

var label = "New row"; // Label to find
var labels = table.rowLabels; // Get all row labels
labels[table.rowIndex(label)] = "New row label"; // Set new label when matches 'label'
table.rowLabels = labels; // Update table row labels

This works the exact same way with column labels:

var label = "New column"; // Label to find
var labels = table.columnLabels; // Get all column labels
labels[table.columnIndex(label)] = "New column label"; // Set new label when matches 'label'
table.columnLabels = labels; // Update table column labels