Table JavaScript Utility Functions
Jump to navigation
Jump to search
This page contains functions to help with writing Table JavaScript for Rules.
To make these functions available when writing a QScript or Rule see JavaScript Reference.
preventAllSignificanceTesting
Prevents any significance testing results being shown in the input table by:
- Setting all cell significance values to false
- Replacing all statistics related to cell significance with NaN
- Replacing all statistics related to column comparisons with a blank
- Preventing the 'Test significance' menu item
- Adding a message to the footer to tell the user that the testing is not available due to the application of the rule named rule_name_string. This is only done when the input add_footer is true.
It is desirable to prevent significance results from being shown on tables where Q's usual significance testing does not apply. An example would be where tables are built by copying statistics from other generated tables. This function should typically be used at the end of the definition of a rule.
belowTableExists()
Returns true if Statistics - Below are available for this table.
In most cases this is sufficient, but for cases where Statistics - Below need to be added to a table when not already present, the code should be pasted directly into the Rule that is using this function. This will be fixed in a future version.
rightTableExists()
Returns true if Statistics - Right are available for this table.
In most cases this is sufficient, but for cases where Statistics - Right need to be added to a table when not already present, the code should be pasted directly into the Rule that is using this function. This will be fixed in a future version.
suppressOutputForRightTable(message)
Suppresses the table output if the user selects any statistics from Statistics - Right.
suppressOutputForBelowTable(message)
Suppresses the table output if the user selects any statistics from Statistics - Below.
setStatisticToNaN(statistic_name)
Replaces all values of the named statistic in the table with NaN.
moveRowAfterComplete(row, after_row)
Move rows in the right_table as well as in the main table when the right_table is shown. If you'd like to move a row to the top of the table use null as the after_row.
moveColumnAfterComplete(col, after_col)
Move columns in the below_table as well as in the main table when the below_table is shown.
deleteColumnComplete(column)
Delete a column from both table and below_table (when it is shown).
deleteRowComplete(row)
Delete a row from both table and right_table (when it is shown).
insertColumnAfterComplete(after_column, label)
Insert a column into both table and below_table (when it is shown).
insertRowAfterComplete(after_row, label)
Insert a row into both table and right_table (when it is shown).
setMarginalStatistic(marginal_table, statistic_name, statistic_array)
Set statistics for right_table or below_table allowing for a difference in the orientation between the array of statistics, statistic_array and the array expected by the usual table.set() function.
statistic_name is a string containing the name or the statistic that you want to set.
marginal_table should be either below_table or right_table.
requireNumericTableDimensionsStatistics(stats_array, number_rows, number_columns, include_nets_in_count, rule_name)
Check the table to which the rule is applied to make sure that:
- The table is a numeric table.
- The table has the right statistics.
- The table has the correct number of rows and columns.
If not, the output of the table is suppressed with a message.
The inputs are:
- stats_array: Array of statistic names to check.
- number_rows: Number of rows needed in the table. Use null if you don't want to specify the number of rows.
- number_columns: Number of columns needed in the table. Use null if you don't want to specify the number of columns.
- include_nets_in_count: If true, the NET and SUM rows/columns of the table are included when checking the number of rows/columns.
- rule_name: The name of the rule that is using this function - used to generate warning messages.
addMainTableStatisticsWhenAvailable(stats_array)
Add statistics from an array to the set of statistics selected on the main table.
roundStats(array, num_decimals)
Round the whole array of statistical values. The array should be in the shape of a Q table.
prepareAllTableStats(table)
Force Q to compute all statistics available before continuing. Can be used for below_table and right_table too.
getStatisticTranslationString(stat)
Return the string which corresponds to the way a statistic will appear in the Statistics - Cells menu when it has been translated in the Table or Project options. For example, if 'Column n' is renamed to 'Column Base', then the translation string is Column Base [Column n].
Source Code
// Prevents any significance testing results being shown on a table by:
//
// 1. Setting all cell significance values to false
// 2. Replacing all statistics related to cell significance with NaN
// 3. Replacing all statistics related to column comparisons with a blank
// 4. Preventing the 'Test significance' menu item
// 5. (Optionally) Adding a message to the footer to tell the user that the testing is not available
var preventAllSignificanceTesting = function(table, rule_name_string, add_footer) {
if (!Q.matrix){
// Returns a new array with the value repeated.
// If you want a 1D array, only supply one length.
// If you want a 2D array, supply both lengths.
Q.matrix = function (value, num_rows, num_cols) {
var rows_array = [];
if (arguments.length > 2) {
for (var row = 0; row < num_rows; row++) {
var col_array = [];
for (var col = 0; col < num_cols; col++)
col_array[col] = value;
rows_array[row] = col_array;
}
} else {
for (var row = 0; row < num_rows; row++)
rows_array[row] = value;
}
return rows_array;
};
}
var num_rows = table.numberRows;
var num_cols = table.numberColumns;
var available_statistics = table.availableStatistics;
var nan_array = Q.matrix(NaN, num_rows, num_cols);
var blank_string_array = Q.matrix(' ', num_rows, num_cols);
var false_flag_array = Q.matrix(false, num_rows, num_cols);
var stats_to_set_as_nan = ['p', 'Corrected p', 'z-Statistic', 't-Statistic', 'Multiple Comparison Adjustment'];
var stats_to_set_to_blank = ['Column Comparisons', 'Columns Compared', "Column Names"];
// Prevent sigificance highlighting
table.cellSignificance = false_flag_array;
// Prevent hypothesis testing
table.hypothesisTestingEnabled = false;
// Replace significance testing statistics with NaNs
for (var j = 0; j < stats_to_set_as_nan.length; j++)
if (available_statistics.indexOf(stats_to_set_as_nan[j]) != -1)
table.set(stats_to_set_as_nan[j], nan_array);
// Replace column comparison statistics with blanks
for (var j = 0; j < stats_to_set_to_blank.length; j++)
if (available_statistics.indexOf(stats_to_set_to_blank[j]) != -1)
table.set(stats_to_set_to_blank[j], blank_string_array);
// Add a note to the footer
if (add_footer) {
var stat_footer_message = "Significance testing is not available due to the rule: " + rule_name_string;
var footers = table.extraFooters;
if (footers.indexOf(stat_footer_message) == -1)
footers.push(stat_footer_message);
table.extraFooters = footers;
}
}
// Returns true if Statistics - Below are available for this table.
function belowTableExists() {
var exists = true;
try {
below_table.statistics;
} catch (e) {
exists = false;
}
return exists;
}
// Returns true if Statistics - Right are available for this table.
function rightTableExists() {
var exists = true;
try {
right_table.statistics;
} catch (e) {
exists = false;
}
return exists;
}
// Replace all statistic values in the named statistic with NaN (or blanks for statistics that have string values)
function setStatisticToNaN(statistic_name) {
var nan_stat = table.get(statistic_name);
var num_rows = table.numberRows;
var num_cols = table.numberColumns;
var nan_value;
if (statistic_name == "Column Comparisons" || statistic_name == "Columns Compared" || statistic_name == "Column Names")
nan_value = "";
else
nan_value = NaN;
for (var row = 0; row < num_rows; row++)
for (var col = 0; col < num_cols; col++)
nan_stat[row][col] = nan_value;
table.set(statistic_name, nan_stat);
}
function suppressOutputForRightTable(message) {
if (rightTableExists())
if (right_table.statistics.length > 0)
table.suppressOutput(message);
}
function suppressOutputForBelowTable(message) {
if (belowTableExists())
if (below_table.statistics.length > 0)
table.suppressOutput(message);
}
// Replace all statistic values in the named statistic with NaN (or blanks for statistics that have string values)
function setStatisticToNaN(statistic_name) {
var nan_stat = table.get(statistic_name);
var num_rows = table.numberRows;
var num_cols = table.numberColumns;
var nan_value;
if (statistic_name == "Column Comparisons" || statistic_name == "Columns Compared" || statistic_name == "Column Names")
nan_value = "";
else
nan_value = NaN;
for (var row = 0; row < num_rows; row++)
for (var col = 0; col < num_cols; col++)
nan_stat[row][col] = nan_value;
table.set(statistic_name, nan_stat);
}
// Move rows for right table as well as the main table.
function moveRowAfterComplete(row, after_row) {
// Main Table
table.moveRowAfter(row, after_row);
// Right table
if (rightTableExists()) {
if (right_table.numberRows == 1) // Right table stored in columns rather than rows
right_table.moveColumnAfter(row, after_row);
else
right_table.moveRowAfter(row, after_row);
}
}
// Move columns for below table as well as the main table.
function moveColumnAfterComplete(col, after_col) {
// Main Table
table.moveColumnAfter(col, after_col);
// Right table
if (belowTableExists()) {
if (below_table.numberColumns == 1) // Below table stored in rows rather than columns
below_table.moveRowAfter(col, after_col);
else
below_table.moveColumnAfter(col, after_col);
}
}
// Delete a column from table and below_table at the same time
function deleteColumnComplete(column) {
// Main table
table.deleteColumn(column);
// Below table
if (belowTableExists()) {
if (below_table.numberColumns == 1) // Below table stored in rows rather than columns
below_table.deleteRow(column);
else
below_table.deleteColumn(column);
}
}
// Delete a row from table and right_table at the same time
function deleteRowComplete(row) {
// Main Table
table.deleteRow(row);
// Right table
if (rightTableExists()) {
if (right_table.numberRows == 1) // Right table stored in columns rather than rows
right_table.deleteColumn(row)
else
right_table.deleteRow(row);
}
}
// Insert a column for table and below_table at the same time
function insertColumnAfterComplete(after_column, label) {
// Main table
table.insertColumnAfter(after_column, label);
// Below table
if (belowTableExists()) {
if (below_table.numberColumns == 1) // Below table stored in rows rather than columns
below_table.insertRowAfter(after_column, label)
else
below_table.insertColumnAfter(after_column, label);
}
}
// Insert a row for table and right_table at the same time
function insertRowAfterComplete(after_row, label) {
// Main Table
table.insertRowAfter(after_row, label);
// Right table
if (rightTableExists()) {
if (right_table.numberRows == 1) // Right table stored in columns rather than rows
right_table.insertColumnAfter(after_row, label)
else
right_table.insertRowAfter(after_row, label);
}
}
// Sets marginal statistics (right_table and below_table) accounting allowing for
// different orientations between the array of statistics and the array
// that is expected by the set function.
function setMarginalStatistic(marginal_table, statistic_name, statistic_array) {
if (marginal_table.numberRows != statistic_array.length)
marginal_table.set(statistic_name, Q.transpose(statistic_array));
else
marginal_table.set(statistic_name, statistic_array);
}
function requireNumericTableDimensionsStatistics(stats_array, number_rows, number_columns, include_nets_in_count, rule_name) {
table.requireNumericTable();
var rule_warning_string = 'The rule \"' + rule_name + '\" requires a table with ';
var cur_rows = include_nets_in_count ? table.numberRows : table.numberRows - table.netRows.length;
var cur_cols = include_nets_in_count ? table.numberColumns : table.numberColumns - table.netColumns.length;
var nets_string = include_nets_in_count ? ' (including NET and SUM)' : ' (excluding NET and SUM)';
if (number_rows != null && cur_rows != number_rows)
table.suppressOutput(rule_warning_string + number_rows + ' rows' + nets_string);
if (number_columns != null && cur_cols != number_columns)
table.suppressOutput(rule_warning_string + number_columns + ' columns' + nets_string);
stats_array.forEach(function (stat) {
if (table.availableStatistics.indexOf(stat) == -1)
table.suppressOutput(rule_warning_string + ' the statistic ' + stat);
});
}
function addMainTableStatisticsWhenAvailable(stats_array) {
var available_stats = table.availableStatistics;
var stats = table.statistics;
stats_array.forEach(function (stat) {
if (available_stats.indexOf(stat) > -1 && stats.indexOf(stat) == -1)
stats.push(stat);
});
table.statistics = stats;
}
var _GLOBAL_STAT_LIST = ["%",
"% Column Responses",
"% Column Share",
"% Excluding NaN",
"% Responses",
"% Row Responses",
"% Row Share",
"% Share",
"% Total Responses",
"% Total Share",
"5th Percentile",
"25th Percentile",
"75th Percentile",
"95th Percentile",
"Average",
"Base n",
"Base Population",
"Coefficient",
"Column %",
"Column Comparisons",
"Column n",
"Column Names",
"Column Population",
"Columns Compared",
"Column Standard Error",
"Column Standard Error of Mean",
"Corrected p",
"Correlation",
"Cumulative %",
"d.f.",
"Effective n",
"Effective Base n",
"Expected Average",
"Expected %",
"Expected Correlation",
"Expected n",
"Index",
"Interquartile Range",
"Labels",
"Lower Confidence Interval",
"Lower Confidence Interval %",
"Maximum",
"Median",
"Minimum",
"Missing n",
"Mode",
"Multiple Comparison Adjustment",
"n",
"n Observations",
"Not duplicate",
"Observation",
"p",
"Population",
"Probability %",
"Range",
"Residual",
"Residual %",
"Row %",
"Row Population",
"Row n",
"Standard Deviation",
"Standard Error",
"Sum",
"Text",
"Text With No Blanks",
"Total %",
"Trimmed Average",
"t-Statistic",
"Unique Text",
"Upper Confidence Interval",
"Upper Confidence Interval %",
"Values",
"z-Statistic"];
var _GLOBAL_STAT_BELOW_LIST = ["Average",
"Sum",
"Column n",
"Column Population",
"Base n",
"Base Population",
"Column Comparisons",
"Column Names",
"Columns Compared",
"5th Percentile",
"25th Percentile",
"75th Percentile",
"95th Percentile",
"Column Standard Error",
"Corrected p",
"Correlation",
"Cumulative %",
"d.f.",
"Effective Base n",
"Expected Correlation",
"Interquartile Range",
"Lower Confidence Interval",
"Maximum",
"Median",
"Minimum",
"Missing n",
"Mode",
"Multiple Comparison Adjustment",
"p",
"Standard Deviation",
"Standard Error",
"Trimmed Average",
"Upper Confidence Interval",
"z-Statistic"];
// Table statistics which get percent-signs when switched on
var _PERCENT_STATS = ['%',
'Total %',
'Column %',
'% Column Responses',
'Row %',
'% Share',
'% Row Share',
'% Column Share'];
// Statistics in Statistics - Below which get significance testing colors and arrows
var _HIGHLIGHTED_BELOW_STATS = ['Average',
'Sum',
'% Share',
'z-Statistic',
'p',
'Corrected p',
'Multiple Comparison Adjustment'];
// Text-based stats related to column comparisons
var _COLUMN_COMPARISON_STATS = ["Column Comparisons", "Column Names", "Columns Compared"]
// round the whole array of stats. array should be in the shape of a Q table.
function roundStats(array, num_decimals) {
includeWeb('JavaScript Utilities');
if (num_decimals !== parseInt(num_decimals, 10))
throw new Error("Expected an integer value.");
if (num_decimals < 0)
throw new Error("Expected a non-negative value.");
var new_values = array.map(function (a) {
return a.map(function (b) {
return roundDecimalNumber(b, num_decimals);
});
});
return new_values;
}
// Force Q to calculate all of the available statistics in the table before continuing
function prepareAllTableStats(table) {
if (table.availableStatistics.indexOf('Minimum') >= 0)
table.get('Minimum');
}
// Return the string which corresponds to the way a statistic will appear in the Statistics - Cells menu
// when it has been translated in the Table or Project options. For example, if 'Column n' is renamed
// to 'Column Base', then the translation string is "Column Base [Column n]"
function getStatisticTranslationString(stat) {
return table.getTranslation(stat) + ' [' + stat + ']';
}
// Internal testing only, please do not use
var specialSmallSampleSuppression = function(data_file_name) {
var all_files = project.dataFiles;
var file_names = all_files.map(function(file) { return file.name});
var aux_file = all_files[file_names.indexOf(data_file_name)];
var small_sample_parameters = aux_file.getVariableByName("Small.sample.suppression").rawValues;
var do_suppression = small_sample_parameters[0];
var min_sample_nps = small_sample_parameters[1];
var min_sample_other = small_sample_parameters[2];
// Set up controls for user input.
form.setHeading('Setting NaN to cells with small samples');
form.setSummary('Setting NaN to cells with small samples: ' + min_sample_nps + " for NPS, " + min_sample_other + " for other statistics");
if (do_suppression == 1){
var is_nps = false;
var has_cc = table.availableStatistics.indexOf("Column Comparisons") > -1;
var sample_stat;
var primary_stat = table.statistics[0];
var blue_question = table.blueQuestion;
var brown_question = table.brownQuestion;
var blue_question_type = blue_question.questionType;
var brown_question_type;
var brown_question_exists = brown_question != "SUMMARY" && brown_question != "RAW DATA";
if (brown_question_exists) {
brown_question_type = brown_question.questionType;
}
if (primary_stat == "n") {
sample_stat = null;
} else if (blue_question_type == "Pick One - Multi") {
if (brown_question_exists && primary_stat == "Column %")
sample_stat = "Column n"
else
sample_stat = "Base n"
} else if (primary_stat == "Column %") {
sample_stat = "Column n";
} else if (primary_stat == "Row %") {
sample_stat = "Row n";
} else if (primary_stat == "%") {
sample_stat = "Base n";
} else if (primary_stat == "Average") {
if (brown_question_exists) {
if (brown_question.name.toLowerCase().indexOf("nps") > -1 || brown_question.name.toLowerCase().indexOf("how likely") > -1)
is_nps = true;
}
if (blue_question.name.toLowerCase().indexOf("nps") > -1 || blue_question.name.toLowerCase().indexOf("how likely") > -1) {
is_nps = true;
}
if (brown_question_type == null) {
sample_stat = "Base n";
} else if (brown_question_type.indexOf("Number") > -1) {
sample_stat = "Row n";
} else if (["Pick One", "Pick Any"].indexOf(brown_question_type) > -1) {
sample_stat = "Column n";
}
}
var min_sample = is_nps ? min_sample_nps : min_sample_other;
if (sample_stat != null) {
var samples = table.get(sample_stat);// Get column sample sizes.
var main_stat = table.get(table.statistics[0]);
if (has_cc)
var column_comparisons = table.get('Column Comparisons');
for (var column = 0; column < table.numberColumns; column++) {// loop through the columns
for (var row = 0; row < table.numberRows; row++) {
// Get the sample size for this cell
var sample_size = samples[row][column];
// If the column's sample size is less than min_column_n, make each cell in the column blank.
if (sample_size < min_sample) {
main_stat[row][column] = NaN;
if (has_cc)
column_comparisons[row][column] = "";
}
}
}
table.set(table.statistics[0], main_stat);
if (has_cc)
table.set('Column Comparisons', column_comparisons);
}
}
}
// This function is designed to make it easier for
// people to write rules in Displayr. The reason is
// that the statistic names in Displayr do not match
// the original names under the hood (which are the
// original names that Q uses and which are required
// for referencing in the JavaScript).
// Use this in Displayr only - it is not needed for Q.
function getOriginalName(displayr_name) {
let q_to_displayr = [['%', '%'],
['% Column Responses','% Column Responses'],
['% Column Share','% Column Share'],
['% Excluding NaN','% Excluding NaN'],
['% of total','% of total'],
['% Responses','% Responses'],
['% Row Responses','% Row Responses'],
['% Row Share','% Row Share'],
['% Share','% Share'],
['% Total Responses','% Total Responses'],
['% Total Share','% Total Share'],
['% within column','% within column'],
['% within row','% within row'],
['25th Percentile','25th Percentile'],
['5th Percentile','5th Percentile'],
['75th Percentile','75th Percentile'],
['95th Percentile','95th Percentile'],
['Average','Average'],
['Coefficient','Coefficient'],
['Column %','Column %'],
['Column Comparisons','Column Comparisons'],
['Column Names','Column Names'],
['Column n','Column Sample Size'],
['Column Standard Error','Column Standard Error'],
['Column Standard Error of Mean','Column Standard Error of Mean'],
['Columns Compared','Columns Compared'],
['Compare to previous period','Compare to previous period'],
['Compare to rest of data','Compare to rest of data'],
['Corrected p','Corrected p'],
['Correlation','Correlation'],
['n','Count'],
['Cumulative %','Cumulative %'],
['d.f.','d.f.'],
['Effective Column n','Effective Column Sample Size'],
['Effective n','Effective Count'],
['Effective Row n','Effective Row Sample Size'],
['Effective Base n','Effective Sample Size'],
['Expected %','Expected %'],
['Expected Average','Expected Average'],
['Expected Correlation','Expected Correlation'],
['Expected n','Expected n'],
['Index','Index'],
['Interquartile Range','Interquartile Range'],
['Lower Confidence Interval','Lower Confidence Interval'],
['Lower Confidence Interval %','Lower Confidence Interval %'],
['Maximum','Maximum'],
['Median','Median'],
['Minimum','Minimum'],
['Missing n','Missing Count'],
['Mode','Mode'],
['Not Duplicate','Not Duplicate'],
['p','p'],
['Probability %','Probability %'],
['Range','Range'],
['Residual','Residual'],
['Residual %','Residual %'],
['Row %','Row %'],
['Row n','Row Sample Size'],
['Base n','Sample Size'],
['Standard Deviation','Standard Deviation'],
['Standard Error','Standard Error'],
['Sum','Sum'],
['SUM','SUM'],
['Total %','Total %'],
['Trimmed Average','Trimmed Average'],
['t-Statistic','t-Statistic'],
['Upper Confidence Interval','Upper Confidence Interval'],
['Upper Confidence Interval %','Upper Confidence Interval %'],
['Values','Values'],
['Column Population','Weighted Column Sample Size'],
['Population','Weighted Count'],
['Row Population','Weighted Row Sample Size'],
['Base Population','Weighted Sample Size'],
['z-Statistic','z-Statistic']]
let original_name = q_to_displayr.filter(function (name_pair) { return name_pair[1] == displayr_name; });
if (original_name.length == 0)
throw "getOriginalName: Cannot find a match for the statistic name '" + displayr_name + "'";
return original_name[0][0];
}
// convert English names of statistics to their translation safely
function translateStats(stats) {
return stats.map(function(s) {
try {
return table.getTranslation(s);
}catch(e) {
return s;
}
});
}
// map translated statistics back to their English/Q name
function untranslateStats(stats, all_translated_stats,all_stats_eng) {
return stats.map(function(s) { return all_stats_eng[all_translated_stats.indexOf(s)]; });
}
// Return false if summary table with no columns
function tableHasColumns() {
return table.columnLabels !== null;
}
// Apply a function over the rows or columns of a table of values.
// Returns an array of true false values, indicating which rows
// (or columns) should be removed.
function identifyRowsOrColumnsToRemove(by_columns, table_values, test_function, params) {
if (by_columns) {
let columns_to_remove = [];
let n_columns = table_values[0].length;
for (col = 0; col < n_columns; col++)
columns_to_remove.push(test_function(table_values.map(x => x[col]), params));
return columns_to_remove;
} else {
return table_values.map(x => test_function(x, params));
}
}
// Returns true if all values in an array are greate than a value
// or are nan
function allValuesGreaterThanOrNaN(array, params) {
return array.every((x => isNaN(x) || x > params.threshold))
}
function isTextStatistic(statistic_name) {
return ["Column Comparisons", "Column Names", "Columns Compared"].indexOf(statistic_name) > -1;
}
function isRTable() {
return !!table.isRTable
}
function excludeRTables() {
if (table.isRTable) {
form.ruleNotApplicable('this Rule does not work on tables created by Calculations');
}
}
// Show ruleNotApplicable error for R tables that are interpreted as 1D,
// e.g, has no statistic attribute or is a matrix with a single column without a name.
// Users should contact user support who could suggest adding a statistic attribute to the R output.
// We (engineering) would also like to know which rule the user was applying in order to determine
// whether/where we should spend effort to make these R tables work with rules.
function excludeRTablesWithoutColumns() {
if (table.isRTable && table.columnLabels === null) {
form.ruleNotApplicable('this Rule does not work with this table created by a Calculation. Contact support if you would like to apply this Rule');
}
}