QScript Table Functions

This page contains functions that are used to obtain information from Tables, manipulate existing tables, or to create new tables.

For built-in functions for working with tables see:

• QScript Table
• QScript DataReduction

To make these functions available when writing a QScript or Rule see JavaScript Reference.

getStatisticsFromTable(table, statistic_names)

This function returns an object whose elements contain the table of values from the input table for each statistic that is named in statistic_names.

For example, the call

var my_stats = getStatisticsFromTable(my_table, ["n","Base n"])

will return an object with two elements. The first element containing the table of n statistics from my_table, and the second element containing the Base n statistics. These two elements can be accessed, respectively, with the calls

my_stats["n"], and my_stats["Base n"]

If a statistic is not found in the table then the function will return a null element instead of a table of values. If the table is empty (for example if all of the respondents have missing values) then the function returns null instead of returning an object containing tables of values.

getMostRelevantStatisticFromTable(table)

This function returns the table of values from the most relevant statistic of the input table.

The most relevant statistics are, in order of preference:

• Column %
• Row %
• %
• Average
• Probability %

getMostRelevantStatisticsFromFirstColumn(table)

This function returns the first column of numbers from the input table. It picks the most relevant statistic for the question type selected in the table. This is most useful for one-dimensional tables, like summaries of Pick One, Pick Any, and Number - Multi questions. This function uses getMostRelevantStatisticFromTable to determine the most relevant statistic.

sortTableDescending(table)

Sorts a table in descending order according the numbers obtained by the function getMostRelevantStatisticsFromFirstColumn.

The following rows will be left at the bottom of the table:

• NET and SUM
• Categories that look like Don't Know options, according to the function isDontKnow.
• Categories that look like Other/Specify options, according to the function isOther.
• Categories that look like None of these options, according to the function isNoneOfThese.
• Categories that look like All of these options, according to the function isAllOfThese.

addStatisticsToTableIfPossible(table, statistic_names)

Given a table and an array of names of statistics, this function will add those statistics to the table if they are available for that table.

addStatisticsToTablesInGroupIfPossible(group_item, statistic_names)

This function applies addStatisticsToTableIfPossible to every table in the input group_item including tables withing sub-groups.

addTableJavaScriptToItem(item, add_js)

Provides a method that scripts can use to add Table JavaScript to a table or chart. It will work on Q4.7 (as Table JavaScript), or on later versions of Q (using rules).

addTableJavaScriptToTablesInGroup(group_item, expression)

This function adds the string in the input expression to the Table JavaScript of every table in the input group_item, including tables in sub-groups. If the table already has Table JavaScript, then the contents of expression will be added on a new line following the existing script.

addTableJavaScriptToTablesInArray(table_array, expression)

Add the JavaScript expression to each table in the input table_array.

getGroupNamesForTablesInArray(table_array, base_group_name)

This function generates the group name for each table in the table_array. Group names are generated by the function getGroupNameForTable.

getGroupNameForTable(table, base_group_name)

The group name is a string that contains the name of the group that contains a table, preceded by the name of the group that that group is a subgroup of, and so on up the report tree. The input base_group_name specifies the name of the highest group level to search, and this name is not included in the output group name.

For example if the report tree contains a group Group A, which in turn contains a group called Group B, which in turn contains a group called Group C, which contains a table, then the full group name for that table is report: Group A: Group B: Group C. Specifying the base_group_name to be report returns the group name Group A: Group B: Group C.

getGroupNamesForTablesAndPlotsInArrayWithType(item_array, base_group_name)

This function generates a group name for each item (table or chart) in the input item_array, and each name also specifies whether the item is a table or a chart. Group names are generated according to the function getGroupNameForTable.

questionsYieldLargeTable(row_q, column_q)

This function returns true if a crosstab between the questions row_q and column_q would take a long time to generate, hence producing a warning message for the user. This function can help you to prevent the calculation of large tables that will interrupt the running of a QScript. column_q can be null to check for large SUMMARY tables. A table is considered large if it will contain 10,000 or more cells.

rowLabelsContainNumbers(table, min_numbers)

Returns true if more than min_numbers row labels in the input table contain numbers. This includes digits and English words for numbers.

rowLabelsLookLikeScale(table)

Returns true if the row labels of the input table look to come from a scale question.

checkTableHasSingleColumnOfNumbers(table_output, measure_name)

Checks a given table output is a single column (i.e. is a one-dimensional SUMMARY table) and provides an alert and returns false if it is not.

removeRules(table)

Remove the rules from a table. Useful when generating temporary tables where the presence of default rules from the project is not desirable.

deleteInsignificantTablesPlots(p_threshold)

Delete any selected tables and plots where none of the non-NET cells have a corrected p value which is smaller than p_threshold

getNetRowsOrColumns(data_reduction, by_columns)

This function searches for and returns the NET and SUM rows in a table.

getSummaryTableOutput(question)

This function generates a temporary summary TableOutput to validate a specified question.

Source Code

includeWeb("JavaScript Text Analysis Functions");
includeWeb("JavaScript Utilities");
includeWeb("QScript Utility Functions");
includeWeb("QScript Questionnaire Functions");

// Extracts an array containing the requested statistics from a table, with NULL for missing statistics
function getStatisticsFromTable(table, statistic_names) {
    const n_statistics = statistic_names.length;
    const statistics = {}; //this is an associative array (same idea as a dictionary)

    let output;
    try {
        output = table.calculateOutput();
    }
    catch {
        // Perhaps there is "no data"
        return null;
    }

    for (let i = 0; i < n_statistics; i++) {
        const statistic_name = statistic_names[i];
        statistics[statistic_name] = output.availableStatistics.indexOf(statistic_name) == -1 ? null : output.get(statistic_name);
    }

    return statistics;
}

// extracts the first column of numbers from the table
function getMostRelevantStatisticFromTable(table) {
    const output = table.calculateOutput();
    if (output.availableStatistics.indexOf("Column %") != -1) {
        return output.get("Column %");
    }
    else if (output.availableStatistics.indexOf("Row %") != -1) {
        return output.get("Row %");
    }
    else if (output.availableStatistics.indexOf("%") != -1) {
        return output.get("%");
    }
    else if (output.availableStatistics.indexOf("Average") != -1) {
        return output.get("Average");
    }
    else if (output.availableStatistics.indexOf("Probability %") != -1) {
        return output.get("Probability %");
    }
    else {
        throw "No relevant statistics available on table: " + table.name;
    }
}

// extracts the first column of numbers from the table
function getMostRelevantStatisticsFromFirstColumn(table) {
    const output = table.calculateOutput();
    const statistics = getMostRelevantStatisticFromTable(table);
    const n_rows = output.numberRows;
    const result = new Array(n_rows); // as the array could be huge, specifying its size up-front
    for (let i = 0; i < n_rows; i++) {
        result[i] = statistics[i][0] + i / 100000.0; //adding a small number to break ties
    }

    return result;
}


// Sort a table according to most important statistic in the first column in descending order
// Most useful for sorting tables with a single column
function sortTableDescending(table) {
    var _a, _b;

    const output = table.calculateOutput();

    // getting data
    const values = getMostRelevantStatisticsFromFirstColumn(table);
    if (values == null) {
        return;
    }

    const vlabels = output.rowLabels;
    const val_dict = {};
    for (let i = 0; i < values.length; i++) {
        val_dict[vlabels[i]] = values[i];
    }

    const labels = (_a = table.primary) === null || _a === void 0 ? void 0 : _a.dataReduction.rowLabels;
    if (labels == null) {
        alert(table.name + ' has no labels');
        return;
    }

    // modifying values based on anchoring rules
    // resorting values based on special anchoring rules
    let labels_values = [];
    for (let i = 0; i < labels.length; i++) {
        // Match labels from dataReduction to output labels
        const label = labels[i];
        let tmp_val = val_dict[labels[i]];

        // NaN values get anchored to the bottom, but
        // above categories with special labels.
        if (isNaN(tmp_val)) {
            tmp_val = -999999999994;
        }

        if (isNonSortable(label) || label == "NET" || label == "SUM") {
            tmp_val = -999999999999;
        }

        labels_values.push({ label: labels[i], value: tmp_val });
    }

    labels_values = labels_values.sort(function (a, b) { return b.value - a.value; });

    // Sort the data reduction
    try {
        const data_reduction = (_b = table.primary) === null || _b === void 0 ? void 0 : _b.dataReduction;
        if (data_reduction) {
            let prev_label = null;
            labels_values.forEach(function (obj) {
                data_reduction.moveAfter(obj.label, prev_label);
                prev_label = obj.label;
            });
        }
    }
    catch (e) {
        log("Error: " + e);
    }
}

function addStatisticsToTableIfPossible(table, statistic_names) {
    const current_stats_shown = table.cellStatistics;
    if (!isArray(statistic_names)) {
        statistic_names = [statistic_names];
    }

    for (let j = 0; j < statistic_names.length; j++) {
        current_stats_shown.push(statistic_names[j]);
    }

    table.cellStatistics = current_stats_shown;
}

function addStatisticsToTablesInGroupIfPossible(group_item, statistic_names) {
    const sub = group_item.subItems;
    const num_sub_items = sub.length;
    for (let j = 0; j < num_sub_items; j++) {
        const current_item = sub[j];
        if (isReportGroup(current_item)) {
            addStatisticsToTablesInGroupIfPossible(current_item, statistic_names);
        }
        else if (isTable(current_item)) {
            addStatisticsToTableIfPossible(current_item, statistic_names);
        }
    }
}

// Provides a method that scripts can use to add Table JavaScript to a
// table or plot.  It will work on Q4.7 (as Table JavaScript), or on
// later versions of Q (as Conditional Formats wrapping the script).
function addTableJavaScriptToItem(item, add_js) {
    if (!project.rules) {
        // Old version of Q that does not support conditional formatting (Q 4.7).
        let item_js = item.tableJavaScript;
        if (item_js == null) {
            item_js = add_js;
        }
        else {
            item_js += "\r\n\r\n" + add_js;
        }
        item.tableJavaScript = item_js;
    }
    else {
        // Modern Q: 4.8.3+
        const rule = project.rules.newCustomRule(add_js, {});
        item.rules.add(rule);
    }
}

// Adds table java script to every table in the input group,
// including all tables in all subgroups. The new expression will
// be added after any table javascript that is already in place.
function addTableJavaScriptToTablesInGroup(group_item, expression) {
    const sub = group_item.subItems;
    const num_sub_items = sub.length;
    for (let j = 0; j < num_sub_items; j++) {
        const current_item = sub[j];
        if (isReportGroup(current_item)) {
            addTableJavaScriptToTablesInGroup(current_item, expression);
        }
        else if (isTable(current_item)) {
            addTableJavaScriptToItem(current_item, expression);
        }
    }
}

// Add a Table JavaScript expression to all tables in the array
function addTableJavaScriptToTablesInArray(table_array, expression) {
    const num_tables = table_array.length;
    let current_item;
    for (let j = 0; j < num_tables; j++) {
        current_item = table_array[j];
        addTableJavaScriptToItem(current_item, expression);
    }
}

function getGroupNamesForTablesInArray(table_array, base_group_name) {
    return table_array.map(t => getGroupNameForTable(t, base_group_name));
}

function getGroupNameForTable(table, base_group_name) {
    let group_item = table.group;
    let name = table.name;
    while (group_item && group_item.name != base_group_name) {
        name = group_item.name + ": " + name;
        group_item = group_item.group;
    }
    return name;
}

function getGroupNamesForTablesAndPlotsInArrayWithType(item_array, base_group_name) {
    return item_array.map(t => {
        return t.type + ": " + getGroupNameForTable(t, base_group_name);
    });
}

function questionsYieldLargeTable(row_q, column_q) {
    var _a, _b, _c, _d;
    const MAX_RECOMMENDED_CELLS = 10000;
    if (column_q === undefined || column_q === "SUMMARY") {
        const q_type = row_q.questionType;
        if (row_q.dataReduction.rowLabels == null) {
            return false;
        }

        if (["Pick One - Multi", "Number - Grid", "Pick Any - Grid"].includes(q_type)) {
            if (row_q.dataReduction.columnLabels == null) {
                // pick-one multi with no data
                return false;
            }
            else {
                return (row_q.dataReduction.rowLabels.length *
                    row_q.dataReduction.columnLabels.length) > MAX_RECOMMENDED_CELLS;
            }
        }
        else {
            return row_q.dataReduction.rowLabels.length > MAX_RECOMMENDED_CELLS;
        }
    }
    else {
        return (((_b = (_a = row_q.dataReduction.rowLabels) === null || _a === void 0 ? void 0 : _a.length) !== null && _b !== void 0 ? _b : 0) *
            ((_d = (_c = column_q.dataReduction.rowLabels) === null || _c === void 0 ? void 0 : _c.length) !== null && _d !== void 0 ? _d : 0)) > MAX_RECOMMENDED_CELLS;
    }
}

function rowLabelsContainNumbers(table, min_numbers) {
    let output;
    try {
        output = table.calculateOutput();
    }
    catch {
        return false;
    }

    const row_labels = output.rowLabels;
    const quantified_labels = row_labels.map(quantify);
    const numeric_labels = quantified_labels.filter(function (x) { return !isNaN(x); });
    return numeric_labels.length > min_numbers;
}

function rowLabelsLookLikeScale(table) {
    let output;
    try {
        output = table.calculateOutput();
    }
    catch {
        return false;
    }

    const row_labels = output.rowLabels;
    return labelsLookLikeScale(row_labels);
}

function checkTableHasSingleColumnOfNumbers(table_output, measure_name) {
    const alert_string = 'Your table measuring ' + measure_name + ' needs to contain a single column of numbers ' +
        '(e.g., the result of a Driver analysis or a SUMMARY from a Pick One, Pick Any, Pick Any - Compact, Number - Multi, Ranking or Experiment question).';
    if (table_output.statistics.length != 1) {
        alert(alert_string);
        return false;
    }

    const values = table_output.get(table_output.statistics[0]);
    if (typeof values[0][0] == 'string') {
        alert(alert_string);
        return false;
    }

    return true;
}

function removeRules(table) {
    table.rules.list.forEach(rule => {
        table.rules.remove(rule);
    });
}

function getNetRowsOrColumns(data_reduction, by_columns) {
    var _a, _b;
    const has_net_finder = !!data_reduction.netRows;
    let nets = [];
    if (has_net_finder) {
        // Q10.5 and higher can get indices of NET rows/columns using
        // built-in functions.
        if (by_columns) {
            nets = data_reduction.netColumns;
        }
        else {
            nets = data_reduction.netRows;
        }
    }
    else {
        // Older versions need to look at the label for "NET" and "SUM"
        if (by_columns) {
            (_a = data_reduction.columnLabels) === null || _a === void 0 ? void 0 : _a.forEach(function (label, index) {
                if (label == "NET" || label == "SUM") {
                    nets.push(index);
                }
            });
        }
        else {
            (_b = data_reduction.rowLabels) === null || _b === void 0 ? void 0 : _b.forEach(function (label, index) {
                if (label == "NET" || label == "SUM") {
                    nets.push(index);
                }
            });
        }
    }

    return nets;
}

function getSummaryTableOutput(question) {
    const temp_table = project.report.appendTable();
    temp_table.primary = question;
    const data_reduction = question.dataReduction;
    const row_labels = data_reduction.rowLabels;

    let output = null;
    if (row_labels && row_labels.length > 0) {
        output = temp_table.calculateOutput();
    }

    temp_table.deleteItem();
    return output;
}

// Unhide rows or columns in the selected table.
// Set rows = true to unhide rows, rows = false to unhide columns.
// Does not work for Banners, Date/Time, or Text data.
function unhideRowsOrColumnsInTable(rows = true) {
    includeWeb("JavaScript Array Functions");
    includeWeb("QScript Selection Functions");
    const user_selections = getAllUserSelections(); // QScript Selection Functions.js
    const selected_tables = user_selections.selected_tables;

    // Require exactly one table selected
    if (!exactlyNSelected(user_selections, "Table", 1)) { // QScript Selection Functions.js
        log("Select a single table before running this option.");
        return;
    }

    const selected_table = selected_tables[0];
    const primary = selected_table.primary;
    const secondary = selected_table.secondary;
    const primary_type = primary.questionType;
    const secondary_type = typeof secondary === "string" ? undefined : secondary === null || secondary === void 0 ? void 0 : secondary.questionType;
    const two_d_questions = ["Pick One - Multi", "Pick Any - Grid", "Number - Grid"];
    const primary_is_2d = two_d_questions.indexOf(primary_type) > -1;
    const secondary_is_2d = secondary_type != null && two_d_questions.indexOf(secondary_type) > -1;

    // Can't handle banners with the current API
    if (rows && primary.isBanner) {
        log("To unhide rows from a Banner, select the Banner under Data Sources and choose DATA VALUES > Reset on the right.");
        return;
    }
    if (!rows && typeof secondary !== "string" && (secondary === null || secondary === void 0 ? void 0 : secondary.isBanner)) {
        log("To unhide columns from a Banner, select the Banner under Data Sources and choose DATA VALUES > Reset on the right.");
        return;
    }
    // Nothing to do if data is Text
    if (rows && primary_type.indexOf("Text") > -1) {
        log("Cannot hide or unhide rows in Text data.");
        return;
    }
    // Nothing to do if data is Date
    if (rows && primary_type == "Date") {
        log("You cannot hide or unhide rows from Date/Time data. To modify which dates are included, select the variable under Data Sources and then use GENERAL > Date/Time on the right.");
        return;
    }
    if (!rows && secondary_type == "Date") {
        log("You cannot hide or unhide columns from Date/Time data. To modify which dates are included, select the variable under Data Sources and then use GENERAL > Date/Time on the right.");
        return;
    }


    // Collect an object of hidden codes
    let hidden_codes = [];
    const primary_dr = primary.dataReduction;
    if (primary_dr != null)
        hidden_codes = primary_dr.hiddenValues().map(function (x) {
            return {
                code: x,
                axis: (primary_is_2d ? x.axis : "Rows"), // If question is 2d, check axis, else axis is Rows
                question: primary
            };
        });

    if (secondary != "SUMMARY" && secondary != "RAW DATA") {
        const secondary_dr = typeof secondary !== "string" ? secondary === null || secondary === void 0 ? void 0 : secondary.dataReduction : undefined;
        if (secondary_dr != null) {
            hidden_codes = hidden_codes.concat(secondary_dr.hiddenValues().map(x => {
                return {
                    code: x,
                    axis: (secondary_is_2d ? x.axis : "Columns"), // If question is 2d, check axis, else axis is Columns
                    question: secondary
                };
            }));
        }
    }

    // Filter out what is hidden to show only rows or columns
    const relevant_axis = rows ? "Rows" : "Columns";
    hidden_codes = hidden_codes.filter(function (obj) {
        return obj.axis == relevant_axis;
    });
    if (hidden_codes.length == 0) {
        log("There are no " + relevant_axis.toLowerCase() + " to unhide in this table.");
        return;
    }

    const codes_to_unihide = selectMany("Select the " + (rows ? "rows" : "columns") + " to unhide:", hidden_codes.map(function (obj) {
        return obj.code.originalLabel;
    }));
    const elements = getElementsOfArrayBySelectedIndices(hidden_codes, codes_to_unihide);
    elements.forEach(obj => {
        const data_reduction = obj.question.dataReduction;
        data_reduction.unhide(obj.code);
    });
}

function largeCrosstabs(row_questions, column_questions) {
    const large_q_pairs = [];
    let row_q, column_q;
    for (let i = 0; i < row_questions.length; i++) {
        for (let j = 0; j < column_questions.length; j++) {
            row_q = row_questions[i];
            column_q = column_questions[j];
            if (!row_q.isValid || !column_q.isValid) {
                continue;
            }
            if (questionsYieldLargeTable(row_q, column_q)) {
                large_q_pairs.push('"' + row_q.name + ' and ' + column_q.name + '"');
            }
        }
    }
    return large_q_pairs;
}

function largeCrosstabsAllowed(row_questions, column_questions) {
    let large_okay = true;
    const large_q_pairs = largeCrosstabs(row_questions, column_questions);
    if (large_q_pairs.length == 1) {
        large_okay = confirm(correctTerminology('The question pair ') + large_q_pairs[0] +
            ' will result in a very large table.\n\n' +
            'Very large tables are not likely to be interesting, will be slow to calculate ' +
            'and will generate a warning message when viewed.\n\n' +
            'Do you wish to continue?');
    }
    else if (large_q_pairs.length > 1) {
        let large_q_pairs_examples = '';
        for (let i = 0; i < Math.min(3, large_q_pairs.length); i++) {
            large_q_pairs_examples = large_q_pairs_examples + large_q_pairs[i] + '\n';
        }
        large_okay = confirm('There are ' + large_q_pairs.length +
            ' very large tables that will be created.\n\n' +
            'Very large tables are not likely to be interesting, will be slow to calculate ' +
            'and will generate a warning message when viewed.\n\n ' +
            'Question pairs that will result in very large tables include:\n' +
            large_q_pairs_examples + '\n' +
            'Do you wish to continue?');
    }
    return large_okay;
}

See also

• QScript
• Online JavaScript Libraries