Yes and no in many forms

• Each system and each person has a preferred way to write yes and no, and files from several of them were combined.
• A checkbox was exported as TRUE and FALSE by one tool and as 1 and 0 by another.
• The field was free text, so people typed whatever felt natural, including x for yes.

• Counting the yes answers finds only the rows that match the one spelling the filter asked for.
• Software that reads the column as true or false rejects the values it does not recognize, or treats every non-empty cell as true.
• Blanks are swept in with no, so people who were never asked are recorded as having declined.

• List the distinct values. A yes and no column should have two, plus blank.
• Lowercase and trim the column, then list again to see which variants are only formatting.
• Look for values that are neither, such as maybe, pending, or a date typed into the wrong column.

• Write down which tokens mean yes and which mean no for this column, and check the list with someone who knows the source.
• Map every token to one pair of values and use that pair everywhere.
• Keep blank as blank unless the source confirms that an empty cell meant no. That needs a decision, not a rule.
• Set aside anything that is neither yes nor no instead of forcing it into one side.

• Inconsistent category labelsA column meant to hold a few fixed values holds many spellings of them.
• Disguised missing valuesA cell is not empty, but what it holds means there is no value: N/A, a dash, 999, or the word none.
• True blanksA cell is really empty, and the file does not say whether the value is unknown, not applicable, or was never asked for.

TermsBoolean, Canonical value, Mapping, Standardization