QlikView Expressor: The Logical Operators: and, or

    Originally published on 07-21-2011 10:31 AM

     

    In expressor datascript, the logical operators – and, or – do not necessarily return Boolean values, but rather the value of one of their inputs.

    • The and operator returns its first input if that input value is false or  nil, otherwise it returns the value of its second input.
    • The or operator returns the value of its first input if that input value  is not false or nil, otherwise it returns the value of its second input.
    • A Boolean value is returned only if the appropriate input is itself a Boolean value.
    • Only the values false and nil are interpreted as false.
    • The values zero, one, and the empty string are all interpreted as non-nil, and therefore true, values.
    • The and operator has higher precedence than the or operator and both of  these operators have lower precedence than the relational and  mathematical operators.

    Since logical expressions can return values, they may be used in situations in which you would not normally think to use a logical expression.  The most unusual example is to use these operators to implement the equivalent of an if..then..else block as in the following  statement.

    input_1 and input_2 or input_3

    Think of input_1 as the conditional statement tested in the if clause. What you enter as input_1 can simply be a reference to a record attribute or a typical conditional statement that includes relational operators.  Then input_2 represents the value returned from the then clause.  And input_3 is the value returned from the else clause.  You can’t use this syntax when the then and else clauses contain multiple statements, but it does have some very interesting uses.

    For example, what if you want to supply a default value if an attribute is nil?  The following statement will do the trick.

    field_name and field_name or default_value

    If field_name contains a non-nil value, this statement will return this value, otherwise it will return default_value.

    Note that you can also nest the conditional statements.  The following statement will return Democrat if the attribute party contains the value Democratic, will return Republican if the attribute party contains the value Republican, and will return Neither a Democrat nor Republican if the attribute party contains any other value.  Be careful to use parentheses to control the precedence in an expression.

    party=="Democratic" and "Democrat" or
            (party=="Republican" and "Republican" or "Neither Democrat nor Republican")