Tables - Banner Tables

Related Videos
	Part 4 of Creating Tables in Q (Video)

This QScript generates banner tables for a list of specified questions. This is an alternative to creating a Banner and then using Basic Tables to generate crosstabs with the banner, and automatically flattens any two-dimensional (grid) questions.

Example

Technical details

You will be asked to specify:

The questions to place in the rows of the tables.
The questions to use in the banner.

Any Pick One - Multi, Pick Any - Grid and Number - Grid questions will be copied and flattened before using them in a crosstab. The flattened copy will contain all of the cells from the original question in a single column or row.

How to apply this QScript

Start typing the name of the QScript into the Search features and data box in the top right of the Q window.
Click on the QScript when it appears in the QScripts and Rules section of the search results.

OR

Select Automate > Browse Online Library.
Select this QScript from the list.

Customizing the QScript

This QScript is written in JavaScript and can be customized by copying and modifying the JavaScript.

Customizing QScripts in Q4.11 and more recent versions

Start typing the name of the QScript into the Search features and data box in the top right of the Q window.
Hover your mouse over the QScript when it appears in the QScripts and Rules section of the search results.
Press Edit a Copy (bottom-left corner of the preview).
Modify the JavaScript (see QScripts for more detail on this).
Either:
- Run the QScript, by pressing the blue triangle button.
- Save the QScript and run it at a later time, using Automate > Run QScript (Macro) from File.

Customizing QScripts in older versions

Copy the JavaScript shown on this page.
Create a new text file, giving it a file extension of .QScript. See here for more information about how to do this.
Modify the JavaScript (see QScripts for more detail on this).
Run the file using Automate > Run QScript (Macro) from File.

JavaScript

includeWeb('QScript Selection Functions');
includeWeb('QScript Table Functions');
includeWeb('QScript Functions to Generate Outputs');
includeWeb('QScript Utility Functions');

createBannerTables()

function createBannerTables() {

    const web_mode = inDisplayr();

    const allowed_column_types = web_mode ? ["Pick One", "Pick Any", "Pick Any - Compact"]
                                          : ["Pick One", "Pick Any", "Pick Any - Compact", "Pick One - Multi", "Pick Any - Grid"];

    let column_q_choices = [];
    let row_q_choices = [];
 
    let data_file = requestOneDataFileFromProject(false);

    // Work out all questions which may be presented to the user 
    let candidate_questions = data_file.questions;
    candidate_questions = candidate_questions.filter(function (q) {
        return !q.isHidden && q.questionType.indexOf("Text") == -1 && q.isValid;
    });

    candidate_questions.forEach(function (q) {
        row_q_choices.push(q);
        if (allowed_column_types.indexOf(q.questionType) > -1 && !q.isBanner) {
            column_q_choices.push(q);
        }
    });

    if (row_q_choices.length == 0 || column_q_choices.length == 0) {
        log(correctTerminology("There are no appropriate questions to use for banner tables."));
        return false;
    }

    let row_questions;
    let column_questions;
    while (!row_questions || !oneOrMoreQuestions(row_questions)) {
        row_questions = selectManyQuestions(correctTerminology('Please select questions to place in the rows:'), row_q_choices).questions;
    }

    while (!column_questions || !oneOrMoreQuestions(column_questions)) {
        column_questions = selectManyQuestions(correctTerminology('Please select questions to place in the banner:'), column_q_choices).questions;
    }

    // Stop early if there will be large tables created and the user chooses not procede. 
    if (!checkForLargeBannerTables(row_questions, column_questions)) {
        return;
    }

    const as_page = onPages("Create banner tables on pages?");

    flattenSelectedQuestions(column_questions);
    flattenSelectedQuestions(row_questions);

    // place each column question inside an array
    column_questions = column_questions.map(function (q) { return [q]; });

    // create banner from column questions
    let banner_name = preventDuplicateQuestionName(data_file, 'BANNER');
    let banner_q;
    if (fileFormatVersion() >= 8.58) {
        banner_q = data_file.createBanner(banner_name, column_questions, false, true);
    } else {
        banner_q = data_file.createBanner(banner_name, column_questions);

        // remove NET from each span
        for (var i = 0; i < banner_q.variables.length; i++) {
            let variable = banner_q.variables[i];
            for (var j = 0; j < column_questions.length; j++)
                if (variable.label == column_questions[j][0].name + ': NET') {
                    variable.deleteVariable();
                    break;
                }
        }
    }


    let output_pairs = row_questions.map(function (q) { return { primary: q, secondary: banner_q }; });
    let page_names = row_questions.map(function (q) { return q.name; });
    let statistics = [];

    let report = createReport("Banner Tables",
                      output_pairs,
                      page_names,
                      statistics,
                      null,
                      [],
                      false,
                      1,
                      false,
                      false,
                      !as_page);

    if (!web_mode) {
        let new_tables = [];
        recursiveGetAllTablesInGroup(report, new_tables);
        new_tables.forEach(function (table) {
            if (!addStat(table, 'Row %') * !addStat(table, 'n')) // non short-circuiting &&
                addStat(table, 'Column n');
        })
    }

    let report_pages = report.subItems.filter(x => x.type != "Text");
    if (fileFormatVersion() > 8.65) {
        project.report.setSelectedRaw([report_pages[0]]);
    }

    return true;
}


function addStat(table, stat) {
    cell_stats = table.cellStatistics;
    cell_stats.push(stat);
    table.cellStatistics = cell_stats;
    return table.cellStatistics.indexOf(stat) > -1;
}

// Use the number of row and column labels in a question's data reduction to work out how many cells will be in the table.
function estimateCellsInQuestion(question) {
    let data_reduction = question.dataReduction;
    let row_labels = data_reduction.rowLabels;
    let col_labels = data_reduction.columnLabels;
    let n_rows = (row_labels == null) ? 1 : row_labels.length;
    let n_cols = (col_labels == null) ? 1 : col_labels.length;
    return n_rows * n_cols;
}

// Determine the size of each table that will be created based on the user's selections and confirm if they want to procede if there are any combinations
// which will create an output which is likely to be problemtic.
function checkForLargeBannerTables(row_qs, column_qs) {
    const MAX_RECOMMENDED_CELLS = 10000;
    let n_columns = arraySum(column_qs.map(estimateCellsInQuestion));
    let estimated_table_sizes = row_qs.map(function (q) { return { question: q, table_size: estimateCellsInQuestion(q) * n_columns } });
    let over_sized = estimated_table_sizes.filter(function (obj) { return obj.table_size > MAX_RECOMMENDED_CELLS });
    let n_large = over_sized.length;
    if (n_large === 0) {
        return true;
    }

    let message = 
`${n_large === 1 ? `One of the selected questions will produce a very large table: ${over_sized[0].question.name}` : `The current selection will create ${n_large} very large tables.`}
Very large tables are not likely to be interesting and may be slow to calculate.
Do you wish to continue?
`
    message = correctTerminology(message);
    return confirm(message);
}