QScript Table Functions

From Q
Jump to navigation Jump to search

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:

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

// TABLES

// Extracts an array containing the requested statistics from a table, with NULL for missing statistics
function getStatisticsFromTable(table, statistic_names) {
    var n_statistics = statistic_names.length;
    var statistics =  {}; //this is an associative array (same idea as a dictionary)
    try {
        var output = table.calculateOutput();
    } catch (e) {
        // Perhaps there is "no data"
        return null;
    }
    for (var i = 0; i < n_statistics; i++) {
        var 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) {
    var output = table.calculateOutput();
    var statistics = null;
    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) {
    var output = table.calculateOutput();
    var statistics = getMostRelevantStatisticFromTable(table);
    var n_rows = output.numberRows;
    var result = new Array(n_rows);//as the array could be huge, specifying its size up-front
    for (var 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 output = table.calculateOutput();
    var n_rows = output.numberRows;
    
    // getting data
    var values = getMostRelevantStatisticsFromFirstColumn(table);
    var num_vals = values.length;
    if (values == null)
        return null;
    var vlabels = output.rowLabels;
    var val_dict = {};
    for (var i = 0; i < values.length; i++)
        val_dict[vlabels[i]] = values[i];
 
    var labels = table.primary.dataReduction.rowLabels;
    if (labels == null)
        alert(table.name + ' has no labels');

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

        // NaN values get anchored to the bottom, but
        // above categroies 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
    {
        var dataReduction = table.primary.dataReduction;
        var prev_label = null;
        labels_values.forEach(function (obj) {
            dataReduction.moveAfter(obj.label, prev_label);
            prev_label = obj.label;
        });
    } catch(e)
    {
        log("Error: " + e);
    }
}

function addStatisticsToTableIfPossible(table, statistic_names) {
    var current_stats_shown = table.cellStatistics;
    if (!isArray(statistic_names))
        statistic_names = [statistic_names];
    for (var 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) {
    var sub = group_item.subItems;
    var num_sub_items = sub.length;
    for (var j = 0; j < num_sub_items; j++) {
        var current_item = sub[j];
        if (current_item.type == 'ReportGroup')
            addStatisticsToTablesInGroupIfPossible(current_item, statistic_names);
        else if (current_item.type == 'Table') {
            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).
        var 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+
        var 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) {
    var sub = group_item.subItems;
    var num_sub_items = sub.length;
    for (var j = 0; j < num_sub_items; j++) {
        var current_item = sub[j];
        if (current_item.type == 'ReportGroup')
            addTableJavaScriptToTablesInGroup(current_item, expression);
        else if (current_item.type == 'Table')
            addTableJavaScriptToItem(current_item, expression);
    }
}

// Add a Table JavaScript expression to all tables in the array
function addTableJavaScriptToTablesInArray(table_array, expression) {
    var num_tables = table_array.length;
    var current_item;
    for (var 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(function (t) { return getGroupNameForTable(t, base_group_name); });
}

function getGroupNameForTable(table, base_group_name) {
    var group_item = table.group;
    var name = table.name;
    while (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(function (t) { 
        return t.type +": " + getGroupNameForTable(t, base_group_name); 
    });
}

function questionsYieldLargeTable(row_q, column_q) {
    const MAX_RECOMMENDED_CELLS = 10000;
    if (column_q === undefined || column_q === "SUMMARY") {
        let 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 (row_q.dataReduction.rowLabels.length *
                column_q.dataReduction.rowLabels.length) > MAX_RECOMMENDED_CELLS;
    }
}

function rowLabelsContainNumbers(table, min_numbers) {
    try {
        var output = table.calculateOutput();
    } catch (e) {
        return false;
    }
    var row_labels = output.rowLabels;
    var quantified_labels = row_labels.map(quantify);
    var numeric_labels = quantified_labels.filter(function (x) { return !isNaN(x); });
    return numeric_labels.length > min_numbers;
}

function rowLabelsLookLikeScale(table) {
    try {
        var output = table.calculateOutput();
    } catch (e) {
        return false;
    }
    var row_labels = output.rowLabels;
    return labelsLookLikeScale(row_labels);
}

function checkTableHasSingleColumnOfNumbers(table_output, measure_name) {
    var 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;
    }
    var 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(function (rule) {
        table.rules.remove(rule);
    });
}

function getNetRowsOrColumns(data_reduction, by_columns) {
    var has_net_finder = !!data_reduction.netRows;
    var 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 && data_reduction.columnLabels)
            data_reduction.columnLabels.forEach(function (label, index) {
                if (label == "NET" || label == "SUM")
                    net_columns.push(index);
            });
        else
            data_reduction.rowLabels.forEach(function (label, index) {
                if (label == "NET" || label == "SUM")
                    net_rows.push(index);
            });
    }
    return nets;
}

function getSummaryTableOutput(question) {
    var temp_table = project.report.appendTable();
    temp_table.primary = question;
    var data_reduction = question.dataReduction;
    var row_labels = data_reduction.rowLabels;
    
    if (row_labels.length > 0) output = temp_table.calculateOutput()
        else output = null;
    
    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();
    let selected_tables = user_selections.selected_tables;

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

    let selected_table = selected_tables[0];
    let primary = selected_table.primary;
    let secondary = selected_table.secondary;
    let primary_type = primary.questionType;
    let secondary_type = secondary.questionType;
    let two_d_questions = ["Pick One - Multi", "Pick Any - Grid", "Number - Grid"];
    let primary_is_2d = two_d_questions.indexOf(primary_type) > -1;
    let secondary_is_2d = 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 Sets and choose DATA VALUES > Reset on the right.");
        return;
    }
    if (!rows && secondary.isBanner) {
        log("To unhide columns from a Banner, select the Banner under Data Sets 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 Sets 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 Sets and then use GENERAL > Date/Time on the right.");
        return;    
    }


    // Collect an object of hidden codes
    let hidden_codes = [];
    let 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") {
        let secondary_dr = secondary.dataReduction;
        if (secondary_dr != null)
           hidden_codes = hidden_codes.concat(secondary_dr.hiddenValues().map(function (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
    let 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;
    }

    let codes_to_unihide = selectMany("Select the " + (rows ? "rows" : "columns") + " to unhide:", hidden_codes.map(function (obj) { return obj.code.originalLabel; }));
    codes_to_unihide = getElementsOfArrayBySelectedIndices(hidden_codes, codes_to_unihide);

    codes_to_unihide.forEach(function (obj) {
        let data_reduction = obj.question.dataReduction;
        data_reduction.unhide(obj.code);
    });
}

function largeCrosstabs(row_questions, column_questions) {
    let large_q_pairs = [];
    let row_q, column_q;
    for (var i = 0; i < row_questions.length; i++) {
        for (var 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) {
    includeWeb("QScript Utility Functions");  // for correctTerminology
    let large_okay = true;
    let 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) {
        var large_q_pairs_examples = '';
        for (var 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

Retrieved from ‘https://wiki2.q-researchsoftware.com/index.php?title=QScript_Table_Functions&oldid=62779’