Modify Cell Content - Replace Small Values With Text

From Q
Jump to navigation Jump to search


This rule allows you to choose a statistic, a value and replacement text, and any cell with a value below that level will be replaced with the specified text.

Example

Here we have chosen to replace any cell that has a Column % value smaller than 2% with "<2%".

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

includeWeb('JavaScript Utilities');
includeWeb('Table JavaScript Utility Functions');

excludeRTables();

let stats = difference(table.availableStatistics, _COLUMN_COMPARISON_STATS);
let translated_stats = translateStats(stats);

// Set up controls for user input.
form.setHeading('Replace Small Values with Text');
let description = form.newLabel('');

let label_stats = form.newLabel('Choose a cell statistic:');
let combobox_stats = form.newComboBox('stats', translated_stats);
combobox_stats.setDefault("Column %");
let min_label = form.newLabel('Value less than:');
let numeric_up_down = form.newNumericUpDown('threshold');
numeric_up_down.setDefault(1);
numeric_up_down.setIncrement(0.1);
let replace_label = form.newLabel('Replace with:');
let text_box = form.newTextBox('text');
text_box.setDefault('-');

form.setInputControls([label_stats, combobox_stats, min_label, numeric_up_down, replace_label, text_box]);
let selected_statistic_translated = combobox_stats.requireValue();
let selected_statistic = stats[translated_stats.indexOf(selected_statistic_translated)];
let min_value = numeric_up_down.requireValue();
let replace_text = text_box.requireValue();
form.setSummary('Replace cells where ' + selected_statistic_translated + ' < ' +
    min_value + ' with ' + replace_text);

if (table.availableStatistics.indexOf(selected_statistic) == -1)
    form.ruleNotApplicable(selected_statistic_translated + " is not available on this table");

let replace_flag = false; // Keep track of whether any cells have been changed.
table.showPercentSign = false;
let tab_stats = table.statistics;
let cell_text = table.cellText;

for (i = 0; i < tab_stats.length; i++) {
    let stat = table.get(tab_stats[i]);
    for (let column = 0; column < table.numberColumns; column++) {
        for (let row = 0; row < table.numberRows; row++) {
            // Get the value and text for this cell
            if (cell_text[row][column] == null)
                cell_text[row][column] = new Array(table.statistics.length);
            let cell_value = stat[row][column];
            // Replace if fits criteria
            if (tab_stats[i] == selected_statistic && cell_value < min_value) {
                stat[row][column] = NaN;
                cell_text[row][column][i] = replace_text;
                replace_flag = true;
            }
            else if (tab_stats[i].indexOf("%") > -1) {
                table.showPercentSign = true;
            }

        }
    }
    table.set(tab_stats[i], stat);
}

table.cellText = cell_text;
table.requireNumericTable();
table.showMissingAs("");

if (replace_flag) {
    let extra_footers = table.extraFooters; // Store the extra footnotes we generate for each changed cell.
    extra_footers.push('Cells with ' + replace_text + ' have ' + selected_statistic_translated +
        ' that is less than ' + min_value + '.');
    // Set the extra footnotes.
    table.extraFooters = extra_footers;
}

See also

Retrieved from "https://wiki.q-researchsoftware.com/index.php?title=Modify_Cell_Content_-_Replace_Small_Values_With_Text&oldid=69403"