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" ) ) )
);
"true"
"true"
"true"
"false"
If( |
|
: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" |
|
); |
|
•
|
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 for details.
|
|
•
|
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 for details.
|
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.