QlikView Expressor: Setting Default Values into Attributes     

    Originally published on 07-21-2011 06:14 AM

     

    Sometimes the data read from a field in a file or database column may be nil or contain an empty string. In this situation, you may want to replace the value with a default value. Basically, you want to execute an if..then..else statement where either the current value or the default value is returned depending on the outcome of the if clause.

    Expressor Datascript provides two very easy ways to implement this logic. Both approaches are compatible with syntax used in the right-hand side of an assignment statement and can, therefore, be employed when scripting using either the Expression Editor or Datascript Editor.

    Review the documentation describing the decision function (see the topic basic functions). This function performs one, or more, comparisons and the first comparison that evaluates to true returns a specific value.

    decision(comparison, default[, comparison2, default, ...][, value])

    If comparison1 is true, the function returns your default value, and so forth. If none of the comparisons return true, the function returns the original. Using this function, you can easily test whether an attribute (att) holds a nil or empty value.

    decision(is.null(att), default, is.empty(att), default, att)

    The first comparison tests whether the attribute holds a nil value and if true returns the value you have specified as the default. Next it determines whether the attribute is empty (for example, the empty string) and if true returns your default value. If neither condition is true, the function returns the current value of the attribute.

    A second approach is to combine the "and" and "or" logical operators into an expression. The datascript logical operators work a little differently than other logical operators. They are not limited to returning boolean values; rather they can return the value of one of their arguments. You should study the topic Relational and logical operators in the product documentation to understand this concept. By combining both logical operators into a single statement, you can mimic the logic of an if..then..else statement.

    (is.null(att) or is.empty(att)) and default or att

    If the attribute is either nil or empty, the first argument to the and comparison returns true, the "and" operator evaluates its second argument, and this statement then returns your default value. If the attribute is neither nil nor empty, the comparison returns false, the second argument to the "and" operator is not evaluated, the "or" operator is evaluated, and this statement returns the original attribute value (att).

    Note that by entering multiple comparisons into the first argument of the "and" operator, you can test multiple conditions. You can also use the same approach in each comparison of the decision function.