QScript Table Functions

From Q
Jump to: navigation, 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.

recursiveGetAllTablesInGroup(group_item, table_array)

This function adds all of the tables in group_item to the array table_array. The function is called recursive because it obtains tables from the subgroups of the specified group by applying itself to each subgroup.

You should always supply an existing array variable:

var group_1_tables = []; recursiveGetAllTablesInGroup(group_1, group_1_tables);

recursiveGetAllTablesAndPlotsInGroup(group_item, table_array)

This function adds all of the tables in group_item to the array table_array. The function is called recursive because it obtains tables from the subgroups of the specified group by applying itself to each subgroup.

You should always supply an existing array variable:

var group_1_tables_plots = []; recursiveGetAllTablesAndPlotsInGroup(group_1, group_1_tables_plots);

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.

questionsYieldLargeCrosstab(row_q, column_q)

This function returns true is 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.

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;
}


// Place all tables in the group into the supplied table_array.
// This function will get tables from all subgroups of the
// specified group_item
function recursiveGetAllTablesInGroup(group_item, table_array) {
    var cur_sub_items = group_item.subItems;
    var new_table_array = [];
    for (var j = 0; j < cur_sub_items.length; j++) {
        if (cur_sub_items[j].type == 'ReportGroup') {
            recursiveGetAllTablesInGroup(cur_sub_items[j], table_array);
        }
        else if (cur_sub_items[j].type == 'Table')  {
            table_array.push(cur_sub_items[j]);
        }
    }
}

// Place all tables and plots in the group into the supplied table_array.
// This function will get tables from all subgroups of the
// specified group_item
function recursiveGetAllTablesAndPlotsInGroup(group_item, table_array) {
    var cur_sub_items = group_item.subItems;
    var new_table_array = [];
    for (var j = 0; j < cur_sub_items.length; j++) {
        if (cur_sub_items[j].type == 'ReportGroup') {
            recursiveGetAllTablesAndPlotsInGroup(cur_sub_items[j], table_array);
        }
        else if (cur_sub_items[j].type == 'Table'  || cur_sub_items[j].type == 'Plot')  {
            table_array.push(cur_sub_items[j]);
        }
    }
}

// Place all tables in the group into the supplied table_array.
// This function will get tables from all subgroups of the
// specified group_item
function recursiveGetAllItemsInGroup(group_item, table_array) {
    var cur_sub_items = group_item.subItems;
    var new_table_array = [];
    for (var j = 0; j < cur_sub_items.length; j++) {
        if (cur_sub_items[j].type == 'ReportGroup') {
            recursiveGetAllItemsInGroup(cur_sub_items[j], table_array);
        }
        else {
            table_array.push(cur_sub_items[j]);
        }
    }
}

// Place all tables in the group into the supplied table_array.
// This function will get tables from all subgroups of the
// specified group_item
function recursiveGetAllGroupsInGroup(group_item, group_array) {
    var cur_sub_items = group_item.subItems;
    for (var j = 0; j < cur_sub_items.length; j++) {
        if (cur_sub_items[j].type == 'ReportGroup') {
            group_array.push(cur_sub_items[j])
            recursiveGetAllGroupsInGroup(cur_sub_items[j], group_array);
        }
    }
}


// Place all items in the group into the supplied table_array.
// This function will get tables from all subgroups of the
// specified group_item
function recursiveGetAllItemsInGroup(group_item, table_array) {
    var cur_sub_items = group_item.subItems;
    var new_table_array = [];
    for (var j = 0; j < cur_sub_items.length; j++) {
        if (cur_sub_items[j].type == 'ReportGroup') {
            recursiveGetAllItemsInGroup(cur_sub_items[j], table_array);
        } else {
            table_array.push(cur_sub_items[j]);
        }
    }
}


// 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) {
    // getting data
    var values = getMostRelevantStatisticsFromFirstColumn(table);
    var num_vals = values.length;
    if (values == null)
        return null;
 
    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 = [];
    for (var i = 0; i < num_vals; i++)  {
        var label = labels[i];
        
        // NaN values get anchored to the bottom, but
        // above categroies with special labels.
        if (isNaN(values[i]))
            values[i] = -999999999994

        if (isOther(label))
            values[i] = -999999999995;
        else if (isNoneOfThese(label))
            values[i] = -999999999996;
        else if (isAllOfThese(label))
            values[i] = -999999999997;
        else if (isDontKnow(label))
            values[i] = -999999999998;
        else if (label == "NET" || label == "SUM")
            values[i] = -999999999999;

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

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

    // Sort the data reduction
    var dataReduction = table.primary.dataReduction;
    var prev_label = null;
    labels_values.forEach(function (obj) {
        dataReduction.moveAfter(obj.label, prev_label);
        prev_label = obj.label;
    });
}

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 questionsYieldLargeCrosstab(row_q, column_q) {
    return row_q.dataReduction.rowLabels.length * column_q.dataReduction.rowLabels.length > 10000;
}

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 deleteInsignificantTablesPlots(p_threshold) {

    function checkItem(item, deleted_tables, deleted_plots, broken_items) {
//        alert(item.name + " : " + item.type)
        var row_indices_without_net;
        var stats;

        // treat plots and tables separately
        if (item.type == 'Plot') {
            if (item.tertiary != null) {
                return null;
            }
            
            if (item.primary == null) {
                broken_items.push(item);
                return null;
            }

            var table = project.report.appendTable();
            table.primary = item.primary;
            if (table.primary.dataReduction.rowLabels.length == 0) { // Question contains no data
                deleted_plots.push(item);
                table.deleteItem();
                return null;
            }
            if (item.secondary != null) {
                table.secondary = item.secondary;
                if (table.secondary.dataReduction != null && table.secondary.dataReduction.rowLabels.length == 0) { // Question contains no data
                    deleted_plots.push(item);
                    table.deleteItem();
                    return null;
                }
            }
            if (item.weight != null) {
                table.weight = item.weight;
            }
            if (item.filters != null) {
                table.filters = item.filters;
            }
            
            var table_output;
            try { 
                table_output = table.calculateOutput() 
            } catch (e) {
                table_output = null;
            };
            if (table_output != null) {
                row_indices_without_net = table_output.rowIndices(false);
                stats = getStatisticsFromTable(table, [stat_name]);
            } else {
                stats = null;
            }
            table.deleteItem(); // Deleting temp table
        } else if (item.type == "WordCloud") {
            deleted_plots.push(item);
            return null;
        } else {
            var table = item;
            //log(item.name + " : " + item.primary.name + " : " + item.secondary.name); 
            
            try { 
                    var table_output = table.calculateOutput();
                    row_indices_without_net = table_output.rowIndices(false);
                    stats = getStatisticsFromTable(table, [stat_name]); 
                } catch (e) {
                    stats = null;
                    broken_items.push(item)
                    return null;
                }     
            
            if (table.primary.dataReduction.rowLabels.length == 0) { // Question contains no data
                deleted_tables.push(item);
                return null;
            }
            if (item.secondary != null) {
                if (table.secondary.dataReduction != null && table.secondary.dataReduction.rowLabels.length == 0) { // Question contains no data
                    deleted_tables.push(item);
                    return null;
                }
            }
  
        }
        
        // If there are no p-values, the item is not significant, delete.
        if (stats != null && stats[stat_name] == null) {
            if (item.type == 'Table')
                deleted_tables.push(item);
            else if (item.type == 'Plot' || item.type == 'WordCloud')
                deleted_plots.push(item);
            return null;
        }

        // remove NET and SUM rows
        var stats_without_net = [];
        row_indices_without_net.forEach(function (index) {
            stats_without_net.push(stats[stat_name][index]);
        });

        var min_value = minWithReplacedNaN(stats_without_net, Number.POSITIVE_INFINITY);

        if (min_value > p_threshold) {
            if (item.type == 'Table')
                deleted_tables.push(item);
            else if (item.type == 'Plot' || item.type == 'WordCloud')
                deleted_plots.push(item);
            return null;
        }
    }


    var stat_name = 'Corrected p';
    var deleted_tables = [];
    var deleted_plots = [];
    var broken_items = [];
    var web_mode = (!!Q.isOnTheWeb && Q.isOnTheWeb());


    try {
        var selected_items = [];
        if (web_mode)
            selected_items = project.report.selectedItems().filter(function (item) { return ["Table", "Plot", "WordCloud"].indexOf(item.type) > -1; });
        else
            selected_items = selectedTablesAndPlots(project.report);

        var is_cancelled = false;
    }
    catch(e) {
        var selected_items = [];
        var is_cancelled = true;
    }

    if (is_cancelled) {
        log('No valid items selected.');
        log('QScript cancelled.');
        log('');
    }
    else {
        selected_items.forEach(function (item) {
            checkItem(item, deleted_tables, deleted_plots, broken_items)
        });

        // Get names of all the stuff to be deleted.
        deleted_tables_names = deleted_tables.map(function (item) { return item.name; });
        deleted_plots_names = deleted_plots.map(function (item) { return item.name; });
        broken_items_names = broken_items.map(function(item) { return item.name; })

        // Combine everything for deletion
        deleted_items = deleted_tables.concat(deleted_plots).concat(broken_items);

        // On the web we need to clear out the pages which are made blank by the removal of tables/plots
        // This is mainly to facilitate the Insert > Report feature being cleanable.
        if (web_mode) {
            var deleted_pages = [];
            var deleted_pages_names = [];
            var selected_raw = project.report.selectedRaw().filter(function (x) { return x.type == "ReportGroup"});
            all_selected_pages = selected_raw;
            selected_raw.forEach(function (x) { recursiveGetAllGroupsInGroup(x , all_selected_pages); });
            
            all_selected_pages.forEach(function (page) {
                var current_sub_items = [];
                recursiveGetAllItemsInGroup(page, current_sub_items);
                current_sub_items = current_sub_items.filter(function (item) { return item.type != "Text"; });
                if (current_sub_items.length > 0) {
                    if (current_sub_items.every(function (item1) { return deleted_items.some(function (item2) { return item1.equals(item2); }); }))
                        deleted_pages.push(page);
                        deleted_pages_names.push(page.name);
                }
            });
            deleted_pages.forEach(function (page) { 
                try {
                    page.deleteItem();
                } catch (e) {

                }
            })
        } 

        // In Q, just delete the selected items.
        deleted_items.forEach(function (item) { 
            try { item.deleteItem(); } catch (e) {} 
        });

        
        if (deleted_tables_names.length > 0) {
            log('The following tables were not significant at the ' + p_threshold + ' level, and have been deleted:');
            log(deleted_tables_names.join("\r\n"))
            log('\r\n');
        }

        if (deleted_plots_names.length > 0) {
            log('The following plots were not significant at the ' + p_threshold + ' level, and have been deleted:');
            log(deleted_plots_names.join("\r\n"))
            log('\r\n');
        }
         

        if (broken_items_names.length > 0) {
            log('The following items were broken, and have been deleted:');
            log(broken_items_names.join("\r\n"));
            log('\r\n');
        } 
        
        if (web_mode && deleted_pages_names.length > 0) {
            log('The following pages were made empty, and have been deleted:');
            log(deleted_pages_names.join("\r\n"))
        }
    }

}

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;
}

See also