In a comparison, missing values typically return missing, not true or false. For this reason, it is very important to include a result that is always true. Suppose that a data table column contains the values 1, 2, 3, and a missing value in column A. A formula in column B sets up the comparison.
New Table( "Testing Comparisons",
Add Rows( 4 ),
New Column( "A",
Numeric,
"Continuous",
Format( "Best", 10 ),
Set Values( [1, 2, 3, .] )
),
New Column( "B", Character, "Nominal", Formula( If( :A, "true", 1, "false" ) ) )
);
The following values are stored in column B:
"true"
"true"
"true"
"false"
The script works like this:
|
If( |
Begins the comparison. |
|
:A, "true", |
If the value of A is nonmissing and nonzero, the result is "true". This comparison is true for the first three rows. |
|
1, "false" |
The value of 1 is always true, so the missing value returns "false". |
|
); |
Closes the comparison. |
Notes:
• When a cell contains a non-empty string, the comparison fails with the first comparison. The following script returns a missing value if a is a string. It works for numeric values.
a = "a";
If( a, "true", "false" );
• When one value is false and another one is missing, Or() returns missing. Use OrMZ() to return false instead. See OrMZ(a, b) in the JSL Syntax Reference.
• When one value is true and another one is missing, And() returns missing. Use AndMZ() to return false instead. See AndMZ(a, b) in the JSL Syntax Reference.
• MatchMZ() is similar to Match() except that missing values are treated as 0.
• IfMZ() is similar to If() except that missing values are treated as 0.
If you know that some values are missing, you can also compare with Is Missing(). The comparison in the preceding example can be rewritten to return "missing" for missing values:
If( :A, "true", Is Missing( :A ), "missing", "false" );
The preceding expression returns "true" when A is nonmissing and nonzero, "missing" when A is missing, and "false" otherwise.
If the missing value could be 0, use the Zero Or Missing() function instead:
Zero Or Missing( A );
This expression returns 1 when A is 0 or missing.
Tip: You cannot compare a known value with an explicitly defined missing value, only with variables, matrices, or other things that could contain missing values.