Sorting Tables By the Largest Column

This table JavaScript sorts the table according to the column that has the largest Column n as found in the Statistics - Below.

There are a number of points to note about how the table will be sorted:

Tables will be sorted in descending order.
If the table only has a single column then it will be sorted.
This script will have no effect when Column n is not available from the Statistics - Below.
If two or more columns have the same value for Column n then the table will be sorted according to the left-most column that is not the NET or SUM column.
Rows that look like they are Don't Know, Other/Specify, All of these, or None of these responses will be left at the bottom of the table, as will NET and SUM rows.
Tables will be sorted to the %, Column %, Average, or Probability % statistic depending on the Question Type of the questions that are shown in the table.

This example can be run in C:\Program Files\Q\Examples\Cola.Q (this may be located on a different place on your computer depending upon how Q was installed). Create a table of Q5. Brand associations by SUMMARY.

var applicable_table; // Prevent an error when no below_table exists
try {
    applicable_table = below_table.availableStatistics.indexOf('Column n') != -1;
} catch (e) {
    applicable_table = false;
}
if (applicable_table) {// If this table has the 'Column n' statistic
    // Choose which statistic to use to sort
    var stat;
    if (table.availableStatistics.indexOf('%') > -1)
        stat = '%';
    else if (table.availableStatistics.indexOf('Column %') > -1)
        stat = 'Column %';
    else if (table.availableStatistics.indexOf('Average') > -1)
        stat = 'Average';
    else if (table.availableStatistics.indexOf('Probability %') > -1)
        stat = 'Probability %';
    else stat = table.statistics[0];
    var max_index = 0;
    if (table.numberColumns > 1) {
        // Obtain Column n from the Statistics - Below and make sure that the resulting array is the correct shape
        var column_ns = below_table.get('Column n');
        if (column_ns.length == 1)
            column_ns = column_ns[0];
        var num_cols = table.numberColumns;
        var column_labels = table.columnLabels;
        // Find largest column n and the index of the column
        var max_col_n = Number.NEGATIVE_INFINITY;
        for (var j = 0; j < num_cols; j++)
            if (!isNaN(column_ns[j]) && column_ns[j] > max_col_n && column_labels[j] != 'NET' && column_labels[j] != 'SUM') {
                max_col_n = column_ns[j];
                max_index = j;
            }  
     }
    // Obtain the values from the column with the largest column n
    // If the row is a 'Other', 'Dont Know', 'None of these', 'All of these'
    // or 'NET' or 'SUM' then assign it a very large value to keep it at the 
    // bottom of the table.
    var values = table.get(stat);
    var labels = table.rowLabels;
    var row_indices = [];// Create an array of the original row numbers, which we will sort.
    var values_to_sort = []; // Array of values from the target column
    for (var row = 0; row < table.numberRows; row++) {
        row_indices.push(row);
        values_to_sort.push(values[row][max_index]);
        var label = labels[row];
        var replacement_value = getBottomAnchoredReplacementValueForLabel(label);
        if (replacement_value != null)
            values_to_sort[row] = replacement_value + row / 100000.0;//adding a small number to break ties
        if (isNaN(values_to_sort[row]))
            values_to_sort[row] = -999999999900; // Treat NaN as a large negative number
    }
    // Now sort the array of row numbers by the statistical values.
    // This 'function' is run many times - to compare each row against another.
    row_indices.sort(function (row_x, row_y) {
        // Get the values for the two rows we are comparing.
        var value_x = values_to_sort[row_x];
        var value_y = values_to_sort[row_y];
        return value_y - value_x;
    });
    table.sortRows(row_indices);// Instruct the table to sort the rows according to the new order.
    var footers = table.extraFooters;
    if (table.numberColumns > 1)
        footers.push('Sorted according to: ' + column_labels[max_index] + ' by JavaScript');
    else
        footers.push('Sorted by JavaScript');
    table.extraFooters = footers;
}
// checks to see if a label contains one of the possible substrings (array)
function containsSubstring(label, possible_substrings) {
    for (var i in possible_substrings)
        if((label).toLowerCase().indexOf(possible_substrings[i]) != -1) // checks if label contains any of the above
            return true;
    return false;
}
// checks to see if a label represents a don't know
function isDontKnow(label) {
    if (containsSubstring(label,['dk','d.k.','don\'t know','dont know','unsure','un-sure','not sure','do not know','no idea','N/A','applicable','not use', 'never use']))
        return true;
    if (label == 'NA' || label == 'na') //removes labels that are NA
            return true;
    return false;
}
// checks to see if a label is an other label
function isOther(label) {
    var label_lower = label.toLowerCase();
    return label_lower.indexOf('other') != -1;
}
// checks to see if a label represents a 'none of these' type option
function isNoneOfThese(label) {
    return containsSubstring(label, ['none','nothing']);
}
// checks to see if a label represents a 'none of these' type option
function isAllOfThese(label) {
    if (containsSubstring(label, ['all of these','any of these','all of them','any of them']))
        return true;
    var lower_label = label.toLowerCase();
    if (lower_label  == 'any' || lower_label == 'all')
        return true;
    return false;
}
// This function returns a very small value for labels that are
// 'Other', 'Dont Know', 'None of These', 'All of these' 
function getBottomAnchoredReplacementValueForLabel(label) {
    if (isOther(label))
        return -999999999995;
    else if (isNoneOfThese(label))
        return -999999999996;
    else if (isAllOfThese(label))
        return -999999999997;
    else if (isDontKnow(label))
        return -999999999998;
    else if (label == 'NET' || label == 'SUM')
        return -999999999999;
    else
        return null;
}

There are some more advanced techniques used in this script. These include:

A try/catch call is made at the start of the script to prevent an error from being generated if the table does not have any Statistics - Below. This happens on tables showing Pick One - Multi questions.
The functions isDontKnow, isOther, isNoneOfThese, isAllOfThese, and getBottomAnchoredReplacementValueForLabel have been used to figure out when a row should be kept at the bottom of the table. This is done by replacing values for such rows with large negative numbers, ensuring that such rows will remain at the bottom of the table when the rows are sorted. These functions are discussed more in JavaScript Text Analysis Functions
A comparison function is being created to sort the row numbers based on the statistics. See Sorting Arrays. This sorts in descending order (smallest value at the top).

Sorting Tables By the Largest Column

See also

Navigation menu

Search