Sort/Reorder Rows or Columns - Sort Category Labels by Largest Category Value

This QScript sorts the categories in the selected Pick One and Pick One - Multi questions according to the underlying value for each category (as shown in the Value Attributes). The categories are sorted so that the category with the highest value placed at the top (or left) of the table, and the category with the lowest value at the bottom (or right) of the table. Categories which have been created by combining merging several categories together will not be sorted.

Example

A question "Brand Satisfaction" with the ratings scale from 1 (Very unsatisfied) to 5 (Very satisfied) in the columns of the table will change into 5 (Very satisfied) to 1 (Very unsatisfied).

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

// Import relevant libraries
includeWeb('QScript Utility Functions');
includeWeb('QScript Questionnaire Functions');
includeWeb('QScript Selection Functions');
includeWeb('QScript Value Attributes Functions');
includeWeb('QScript Functions to Generate Outputs');

// Get data files
var selected_datafiles = dataFileSelection();

// Generate a list of applicable questions along with the highest and lowest category labels
var relevant_questions = getAllQuestionsByTypes(selected_datafiles, ["Pick One", "Pick One - Multi"]);
var num_questions = relevant_questions.length;
var question_label_strings = [];
var highest_and_lowest_label;
for (var j = 0; j < num_questions; j++) {
    highest_and_lowest_label = getHighestAndLowestValueAndLabel(relevant_questions[j]);
    question_label_strings.push(truncateStringForSelectionWindow(relevant_questions[j].name) + "  (" 
        + highest_and_lowest_label.lowest + " ... " + highest_and_lowest_label.highest + ")");
}

// Prompt the user to select questions
selected_indices = selectMany("Please choose which questions you would like to sort:\r\n(highest and lowest value labels are shown in brackets)", question_label_strings);
selected_questions = getElementsOfArrayBySelectedIndices(relevant_questions, selected_indices);
var num_selected = selected_questions.length;

// Apply the sort to each question
selected_questions.forEach(function (q) {
    sortDataReductionAccordingToLargestValues(q, false);
});

// Generate tables and log
generateGroupOfSummaryTables('Sorted Questions', selected_questions);

log("Tables showing the sorted questions have been added to a group in your report.");



// Place the category with the largest value at the top (or left) of the table.
// Only for Pick One and Pick One - Multi questions.
// If use_source is true, then the source values will be used. Otherwise current values 
// from the Value Attributes will be used.
function sortDataReductionAccordingToLargestValues(question, use_source) {
    
    // Data Reduction value map to use for determining maximum values
    // If the value_array has more than one element, this indicates that
    // the array corresponds to merged categories and so should not be sorted (returns -infinity).
    // If the value_array contains a single element then this function
    // returns that value (which is a source value) or maps that source
    // value to the value currently set in the Value Attributes
    var drValueMapForMax = function(value_array, attributes, use_source) {
        if (value_array.length > 1)
            return Number.NEGATIVE_INFINITY;
        else if (!use_source)
            return attributes.getValue(value_array[0]);
        else 
            return value_array[0];
    }

    // Only Pick One and Pick One - Multi allowed
    checkQuestionType(question, ["Pick One", "Pick One - Multi"]);

    var data_reduction = question.dataReduction;
    var attributes = question.valueAttributes;
    
    // Count the number of rows or columns that correspond to values (and not
    // variables). This includes any NET rows/columns
    var num_vals;
    if (!data_reduction.transposed)
        num_vals = data_reduction.rowLabels.length;
    else
        num_vals = data_reduction.columnLabels.length;

    // Sort the data reduction by iterating over the rows, placing the category with the largest value
    // into the current row before moving on to the next row.
    var max = data_reduction.getUnderlyingValues(0);
    var max_index = 0;
    var cur_val;
    for (var row = 0; row < num_vals; row++) {
        max_val = drValueMapForMax(data_reduction.getUnderlyingValues(row), attributes, use_source);
        max_index = row;
        for (var j = row + 1; j < num_vals; j++) {
            cur_val = drValueMapForMax(data_reduction.getUnderlyingValues(j), attributes, use_source);
            if (cur_val > max_val) {
                max_val = cur_val;
                max_index = j;
            }
        }
        if (max_index != row) {
            if (row == 0)
                data_reduction.moveAfter(max_index, null);
            else
                data_reduction.moveAfter(max_index, row - 1);
        }
    }
}

Prior to the 15th of December, 2015, this page was known as Sort - Sort Category Labels by Largest Category Value