Modify Whole Table or Plot - Hide Empty Rows and Columns

From Q
Jump to navigation Jump to search

This rule hides rows or columns for tables and charts where the primary statistic for the table is always 0% or NaN. The primary statistic is the first statistic on the Statistics - Cells list for the table. A note is added to the footer when any rows or columns have been removed.



How to apply this rule

For the first time in a project

  • Select the table(s)/chart(s) that you wish to apply the rule to.
  • Start typing the name of the Rule into the Search features and data box in the top right of the Q window.
  • Click on the Rule when it appears in the QScripts and Rules section of the search results.


  • Select Automate > Browse Online Library.
  • Choose this rule from the list.

Additional applications of the rule

  • Select a table or chart that has the rule and any table(s)/chart(s) that you wish to apply the rule to.
  • Click on the Rules tab (bottom-left of the table/chart).
  • Select the rule that you wish to apply.
  • Click on the Apply drop-down and choose your desired option.
  • Check New items to have it automatically applied to new items that you create. Use Edit > Project Options > Save as Template to create a new project template that automatically uses this rule.

Removing the rule

  • Select the table(s)/chart(s) that you wish to remove the rule from.
  • Press the Rules tab (bottom-right corner).
  • Press Apply next to the rule you wish to remove and choose the appropriate option.

How to modify the rule

  • Click on the Rules tab (bottom-left of the table/chart).
  • Select the rule that you wish to modify.
  • Click Edit Rule and make the desired changes. Alternatively, you can use the JavaScript below to make your own rule (see Customizing Rules).


You can find a simpler version of this code, which does not contain the controls, here.

includeWeb("QScript Utility Functions");
includeWeb("Table JavaScript Utility Functions");

// This rule does not work with R tables where columns are not recognised,
// so we disable the rule where there is more than one statistic (column).
if (table.statistics.length > 1) {

form.setSummary("Hide empty rows and columns");
form.setHeading("Hide Empty Rows and Columns");
let description = form.newLabel("Hide any rows or columns where all of the values are zero, NaN, or blank.");

// Rule isn't applicable if there are more than 2000 rows or columns
if (table.numberRows > MAX_N_ROWS_OR_COLS || table.numberColumns > MAX_N_ROWS_OR_COLS) {
    let in_displayr = inDisplayr();
    let menu_loc = in_displayr ? "" : "Create > ";
    let message = "This rule does not work with tables with more than " + MAX_N_ROWS_OR_COLS +
                  " rows or columns. To efficiently hide empty rows or columns on very large " +
                  "tables, select " + menu_loc + "Table > Paste or Enter Table, select this " +
                  "table as the Data Source, and then use the options in ROW MANIPULATIONS " +
                  "and/or COLUMN MANIPULATIONS.";

// Returns true if all elements of the array are zero
function arrayElementsAllZero(array) {
    return array.filter(function(x) { return Math.abs(x) > 0; } ).length == 0;

// Returns true if all elements of the array are NaN
function arrayElementsAllNaN(array) {
    return array.filter(function(x) { return !isNaN(x); } ).length == 0;

function arrayElementsNotSpecifiedStrings(array, strings) {
    return array.filter(function (x) { return strings.indexOf(x) == -1; }).length == 0;

// Obtain the primary statistics to check
let primary_statistic = table.availableStatistics[0];
let values = table.get(primary_statistic);

// Check to see if the primary statistic is a % statistic.
let is_percentage = primary_statistic.indexOf('%') != -1;

// Check to see if the table is showing a Text question
let is_text = primary_statistic == "Text";
let empty_strings = [""];

// Check for empty rows
let empty_rows = [];

for (let j = 0; j < table.numberRows; j++) {
    let current_row_values = values[j];
    if ( (is_percentage && arrayElementsAllZero(current_row_values)) || (arrayElementsAllNaN(current_row_values) && !is_text) || (is_text && arrayElementsNotSpecifiedStrings(current_row_values, empty_strings)))

// Check for empty columns
let empty_columns = [];

for (let j = 0; j < table.numberColumns; j++) {
    let current_column_values = [];
    for (let k = 0; k < table.numberRows; k++)
    if ((is_percentage && arrayElementsAllZero(current_column_values)) || (arrayElementsAllNaN(current_column_values) && !is_text) || (is_text && arrayElementsNotSpecifiedStrings(current_column_values, empty_strings)))

// Remove empty rows
if (empty_rows.length > 0)
    for (let j = empty_rows.length - 1; j > -1; j--)

// Remove empty columns
if (empty_columns.length > 0)
    for (let j = empty_columns.length - 1; j > -1; j--)

// Add a note to the footer
if (!isRTable()) {
    if (empty_rows.length > 0 || empty_columns.length > 0) {
        let current_footers = table.extraFooters;
        current_footers.push("Some empty rows or columns have been removed");
        table.extraFooters = current_footers;

See also