iff(C1;R1;C2;R2;...;D)

Returns the value corresponding to the first condition that evaluates to true; if no conditions are true, it returns the specified default value. (Available as of version 18.40)

Description

iff() has the same syntax and semantics as if(), but is potentially more memory efficient and often much faster as the number of arguments increases. While if() evaluates all arguments at once, iff() evaluates arguments one at a time, using only as many rows as necessary, potentially skipping arguments entirely if at least one condition has already been satisfied at each row.

Syntax

iff(C1;R1;C2;R2;...;D)

Input

Argument Type Description
C[N] integer A scalar value or the name of a column

The value of C[N] can only be 0 or 1 or an expression that evaluates to one of those values.
R[N] any The value to return if C[N] evaluates to 1

The values specified for all instances of R must be the same type (integer, decimal, or text).
D any The default return value (if none of the instances of C evaluate to 1)

The value specified for D must be the same type as the values specified for all instances of R.
Note: There must be an odd number of arguments (i.e., a series of C and R pairs and one D argument).
   

Return Value

Returns a numeric or text value (depending on the type of the elements specified for R and D) corresponding to the following:
  • If C1 is 1, return R1; else if C2 is 1, return R2; and so on.
  • If none of the conditions specified by C[N] evaluate to 1, return D.

If any of the values corresponding to C[N] are N/A, an error is returned.

Sample Usage

C1 R1 D iff(C1;R1;D)
0 5 10 10
1 'true' 'false' 'true'
0 100 NA NA
C1 R1 C2 R2 D iff(C1;R1;C2;R2;D)
0 'one' 1 'two' 'three' 'two'