Modify Footers - Display Which Columns Are Being Compared

From Q
Jump to: navigation, search

This rule adds a description of the sets of columns that are being compared by the Column Comparisons to the footer of the table.

Example

ComparisonsInFooter1.png

How to apply this rule

For the first time in a project

  • Select the table(s)/chart(s) that you wish to apply the rule to.
  • Start typing the name of the Rule into the Search features and data box in the top right of the Q window.
  • Click on the Rule when it appears in the QScripts and Rules section of the search results.

OR

  • Select Automate > Browse Online Library.
  • Choose this rule from the list.

Additional applications of the rule

  • Select a table or chart that has the rule and any table(s)/chart(s) that you wish to apply the rule to.
  • Click on the Rules tab (bottom-left of the table/chart).
  • Select the rule that you wish to apply.
  • Click on the Apply drop-down and choose your desired option.
  • Check New items to have it automatically applied to new items that you create. Use Edit > Project Options > Save as Template to create a new project template that automatically uses this rule.

Removing the rule

  • Select the table(s)/chart(s) that you wish to remove the rule from.
  • Press the Rules tab (bottom-right corner).
  • Press Apply next to the rule you wish to remove and choose the appropriate option.

How to modify the rule

  • Click on the Rules tab (bottom-left of the table/chart).
  • Select the rule that you wish to modify.
  • Click Edit Rule and make the desired changes. Alternatively, you can use the JavaScript below to make your own rule (see Customizing Rules).

JavaScript

// Report the sets of column comparisons in the footer


includeWeb("JavaScript Array Functions");
var regex = /[A-z]{1}\d*/g;
form.setHeading("Display Which Columns Are Being Compared");
form.setSummary("Display which columns are being compared");
var description = form.newLabel("Adds information about which sets of columns are compared to the table footer.");
description.lineBreakAfter = true;
let within_spans_checkbox = form.newCheckBox('cws', "Comparisons within spans");
within_spans_checkbox.setDefault(true);
form.setInputControls([description, within_spans_checkbox]);

if (table.availableStatistics.indexOf("Column Comparisons") == -1)
    form.ruleNotApplicable("column comparisons are not available on this table");

var column_names = table.get("Column Names");
var columns_compared = table.get("Columns Compared");

// Figure out if we can obtain the "Columns Compared" information in this table,
// and which row to use
var cc_row_index = 0;
var found_cc = false;
var row = 0
while (row < table.numberRows && !found_cc) {
    var this_row_cc = columns_compared[row];
    if (this_row_cc.some(function (x) { return x.length > 0; })) {
        cc_row_index = row;
        found_cc = true;
    }
    row ++;
}

if (!found_cc)
    form.ruleNotApplicable("information about which columns are compared was not found in this table");

// Work out if column names are recycled


// Get list of spans. If there are no spans
// then make one big pseudo span. Any indices
// out side of spans are also included in a psuedo span
// Sets of column comparisons will be determined within
// each span
// Ignore spans if "Comparisons within spans" is unticked
var spans = table.columnSpans;
if (spans.length == 0 || !within_spans_checkbox.getValue())
    spans = [{ indices: table.columnIndices(true), label: "" }];
else {
    var indices_included = [];
    var indices_not_in_spans = [];
    spans.forEach(function (span_obj) { indices_included = indices_included.concat(span_obj.indices) });
    for (var j = 0; j < table.numberColumns; j++) {
        if (indices_included.indexOf(j) == -1)
            indices_not_in_spans.push(j);
    }
    if (indices_not_in_spans.length > 1)
        spans.push({ indices: indices_not_in_spans, label: "Not in span" });
}

// Remove spans with a single column.
// Rule assumes comparisons are done within span.
spans = spans.filter(function (span) { return span.indices.length > 1; })

// Sort spans from largest to smallest
// spans = spans.sort(function (a, b) { return b.indices.length - a.indices.length; });

// Get text for all spans
var comparison_sets = spans.map(generateComparisonSets);

// Figure out if columns names are recycled
var all_cnames = column_names[cc_row_index];
var cnames_recycled = arrayHasDuplicateElements(all_cnames);

// If column names are not recyled then remove any comparison sets which are wholly subsets of other comparison sets
// as these are redundant
if (!cnames_recycled) {
    var unique_comparison_sets = [];
    comparison_sets.forEach(function (set1) {
        if (!unique_comparison_sets.some(function (set2) {
            return set1.comparisons.filter(function (comp) { return set2.comparisons.indexOf(comp) == -1; }).length == 0;
        }))
            unique_comparison_sets.push(set1);
    });
    comparison_sets = unique_comparison_sets;
} 

// Paste all the text together
var comparison_text;
if (comparison_sets.length == 1) {
    comparison_text = [comparison_sets[0].comparisons.join(", ")];
} else {
    comparison_text = comparison_sets.map(function (set) {
        return (set.span == "" ? "" : set.span + ": ") + (set.comparisons.join(", "));
    });
}


// Add to footer
var footers = table.extraFooters;
footers.push("Comparisons: " + comparison_text.join(", "));
table.extraFooters = footers;

// Check if two arrays of column letters match identically
function setsMatch(s1, s2) {
    if (s1.length != s2.length)
        return false;
    return s1.sort().join("") == s2.sort().join("");
}

// Given a set [A,B,C] we can merge element D
// if all combinations AD, BD, CD are found in all_pair_strings
function canMergeElementIntoSet(set, element, all_pair_strings) {
    return set.every(function (x) {
                        var new_pair = [x, element];
                        new_pair.sort();
                        return all_pair_strings.indexOf(new_pair[0] + new_pair[1]) > -1;
                    });
}


// The purpose of this function is to work through pairwise comparisons and
// group them into sets of comparisons.
// For example, if we have comparisons:
// * [A,B]
// * [B,C]
// * [A,C]
// * [C,D]
// Then the resulting comparisons should be [A,B,C] and [C,D].
// This is because each of A, B, C are compared with each other,
// but because D is not compared with A or B, it remains in a 
// separate comparison [C,D] only.
function generateComparisonSets(span_obj) {
    
    
    // Find all pairwise comparisons present in this span
    // Building up an array of pairs like [[A,B], [B,C], [D,E]]
    var all_pairs = [];
    span_obj.indices.forEach(function (index) {
        //log(columns_compared[cc_row_index][index]);
        var ccs = [];
        if (columns_compared[cc_row_index][index].length > 0)
            ccs = columns_compared[cc_row_index][index].match(regex).sort();
        var cname = column_names[cc_row_index][index];
        ccs.forEach(function (str) {
            if (cname < str) {
                all_pairs.push([cname, str]);    
            } else {
                all_pairs.push([str, cname]);
            }
        });
    });

    // Whittle down to just the unique set of pairs
    var unique_pairs = [];
    all_pairs.forEach(function (p) {
        if (!unique_pairs.some( function (pp) { return setsMatch(p, pp); })) {
            unique_pairs.push(p);
        }
    });

    // To facilitate easier checking of pairs, turn each pair [A,B] into a string AB
    // The way this has been set up so far, each pair is in alphabetical order already
    var all_pair_strings = unique_pairs.map(function (p) { return p[0] + p[1]; });
    
    // Duplicate the array of unique pairs
    var remaining_pairs = unique_pairs.slice(0);
    
    // Initialise an empty array to store the unique sets of column comparisons
    var completed_sets = [];

    // Work through the array of pairs.
    // For each pair, work through and figure out which other pairs can be combined to form a larger set.
    // When a pair can be combined, remove it from the set of comparisons to consider in the future.
    while (remaining_pairs.length > 0) {

        // At the start of each loop, rip off the first pair as the base for a new set
        var current_set = remaining_pairs.shift();
        // Loop through all remaining pairs and attempt to merge into current_set
        for (var j = remaining_pairs.length - 1; j > -1; j--) {
            var current_pair = remaining_pairs[j];
            
            var index_0 = current_set.indexOf(current_pair[0]);
            var index_1 = current_set.indexOf(current_pair[1]);
            

            if (index_0 > -1 && index_1 > -1) { // Both letters are already in the current set
                remaining_pairs.splice(j, 1); // Remove this pair from the sets to try to combine
            } else if (index_0 > -1) { // The first element in the current pair is in the set, so the second letter must be able to be merged
                if (canMergeElementIntoSet(current_set, current_pair[1], all_pair_strings)) {
                    current_set.push(current_pair[1]); // Add element to set
                    remaining_pairs.splice(j, 1); // Remove this pair from the sets to try to combine
                }
            } else if (index_1 > -1) { // The second element in the current pair is in the set, so the first letter must be able to be merged
                if (canMergeElementIntoSet(current_set, current_pair[0], all_pair_strings)) {
                    current_set.push(current_pair[0]); // Add element to set
                    remaining_pairs.splice(j, 1); // Remove this pair from the sets to try to combine
                }
            } else { // Neither letters are present in the current set, so both must be able to be merged
                if (canMergeElementIntoSet(current_set, current_pair[0], all_pair_strings) && canMergeElementIntoSet(current_set, current_pair[1], all_pair_strings)) {
                    current_set.push(current_pair[0]);
                    current_set.push(current_pair[1]);
                    remaining_pairs.splice(j, 1); // Remove this pair from the sets to try to combine
                }
            }
        }

        // Once finished checking all pairs against the current set
        // add the current set to the completed sets
        completed_sets.push(current_set.sort());

        // At the end of each loop, if there is one pair left
        // add it to the sets. No need to check anything else
        if (remaining_pairs.length == 1) {
            completed_sets.push(remaining_pairs[0]);
            remaining_pairs = [];
        }
    }

    // Join sets of letters together with / as they normally appear in Q
    var comparison_strings = completed_sets.map(function (set) { return set.join("/")});
    comparison_strings.sort(function (a, b) {
        if (b[0] < a[0])
            return 1;
        else if (a[0] < b[0])
            return -1;
        else
            return 0;
        
    });
    return {span: span_obj.label, comparisons: comparison_strings}
}

See also