Modify Cell Content - Blank Cells with Small Values
Jump to navigation
Jump to search
Q Technical Reference
Q Technical Reference
Q Technical Reference > Creating And Modifying Tables
Q Technical Reference > Setting Up Data > Creating New Variables
Q Technical Reference > Updating and Automation > Automation Online Library
Q Technical Reference > Updating and Automation > JavaScript > Table JavaScript and Plot JavaScript
Rule Online Library
This rule allows you to choose a statistic and a value, and any cell with a value below that level will become blank.
Example
Here we have chosen to blank any cell with a Column % value smaller than 10%.
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');
// In R tables where columns are not recognised, the whole row is blanked,
// so we disable the rule where there is more than one statistic (column).
if (table.statistics.length > 1) {
excludeRTablesWithoutColumns();
}
let stats = difference(table.availableStatistics, _COLUMN_COMPARISON_STATS);
let translated_stats = translateStats(stats);
// Set up controls for user input.
form.setHeading('Blank Cells with Small Values');
let description = form.newLabel("Prevents results from being shown within cells when they fall below a specified value.");
description.lineBreakAfter = true;
let label_stats = form.newLabel('Choose a cell statistic:');
let combobox_stats = form.newComboBox('stats', translated_stats);
combobox_stats.setDefault(translated_stats[0]);
combobox_stats.lineBreakAfter = true;
let label = form.newLabel('Minimum Value:');
let numeric_up_down = form.newNumericUpDown('threshold');
numeric_up_down.setDefault(30);
numeric_up_down.setIncrement(1);
numeric_up_down.setMaximum(1000000);
numeric_up_down.lineBreakAfter = true;
let nan_checkbox = form.newCheckBox("cbnan", "Also set values of statistic to NaN");
nan_checkbox.setDefault(true);
form.setInputControls([description, label_stats,combobox_stats,label, numeric_up_down, nan_checkbox]);
//form.setOutputControls([]);
let selected_stat_translated = combobox_stats.requireValue();
let selected_statistic = stats[translated_stats.indexOf(selected_stat_translated)];
let min_value = numeric_up_down.requireValue();
form.setSummary('Blank cells where ' + selected_stat_translated + ' < ' + min_value);
if (table.availableStatistics.indexOf(selected_statistic) == -1)
form.ruleNotApplicable(selected_stat_translated + " is not available on this table");
let blank_flag = false; // Keep track of whether any cells have been blanked.
let values = table.get(selected_statistic);// Get statistic values in all the cells
for (let column = 0; column < table.numberColumns; column++) {// loop through the columns
for (let row = 0; row < table.numberRows; row++) {
let stat_value = values[row][column];
// If the cells selected statistics is less than min_value, make each cell blank.
if (stat_value < min_value) {
table.blankCell(row, column);
values[row][column] = NaN;
blank_flag = true;
}
}
}
if (nan_checkbox.getValue())
table.set(selected_statistic, values);
// Store the extra footnotes we generate for each blank cell.
if (blank_flag && !isRTable()) {
let extra_footers = table.extraFooters;
extra_footers.push('Blank cells have ' + selected_stat_translated +
' that is smaller than ' + min_value + '.');
table.extraFooters = extra_footers;
}
See also
- User Input for Rules for technical information on Rules.
- Rule Online Library for other examples of Rules.
- Table JavaScript and Plot JavaScript for the JavaScript that is used to write custom rules.
- JavaScript for information about the JavaScript programming language.
- Modify Cell Content - Blank Cells with Small Sample Sizes
Q Technical Reference
Q Technical Reference
Q Technical Reference > Creating And Modifying Tables
Q Technical Reference > Setting Up Data > Creating New Variables
Q Technical Reference > Updating and Automation > Automation Online Library
Q Technical Reference > Updating and Automation > JavaScript > Table JavaScript and Plot JavaScript
Rule Online Library