QlikView Expressor: Working with Constraints

    Originally published on 07-21-2011 04:00 AM

     

    Constraints are business rules that are applied at the level of an attribute in a composite type.  Whenever you are working with a composite type, whether it is within the Output Type Editor of the Transform Editor or in the Type Editor, or with an Atomic Type within the Type Editor, you may associate a constraint with an attribute. Since within a composite type each attribute represents either a local or shared Atomic Type, a constraint is actually a business rule that becomes part of the definition of an Atomic Type.  If you rework the constraints associated with an attribute or its underlying shared composite type, you have also changed any Schema artifacts that refer to the composite type containing the reworked Atomic Type and you will need to open all dataflows that use this Schema artifact and modify the output types associated with each Transform operator.  This generally will not affect your scripting, but it may impact how you want to handle a record that does not satisfy a constraint.

    Depending on the datatype underlying an Atomic Type, the possible constraints vary.  For example, if string is the underlying type, you may specify the length of the value, allowed values, or a character pattern that the value must match, while for a numeric type, you can enforce the position of a decimal point, maximum and minimum values, the number of significant digits, or allowed values.

    Once you specify one or more constraints for an Atomic Type, expressor will test the attribute's value against these specifications as an Input or Transform operator emits a record.  If one of the constraints is violated, a corrective action will be applied.  Again, the type of corrective action depends on the data type underlying the attribute. Corrective actions available to all types are to throw (escalate) the error up to the operator for resolution, to replace the offending value with null, or to replace the offending value with a default value. With  string values, you may alter the length of the value by either truncating characters from the left or right ends or padding either the left or right ends, and numeric values give you the additional option of  rounding.

    If the corrective action is to be applied by the operator, there are multiple options that vary depending on type of operator.

    • For the Read File, Read Table, SQL Query, and Transform operators, there are five options.
      • Abort the dataflow
      • Skip the record containing the offending value
      • Reject the record containing the offending value
      • Skip the offending record and all following records
      • Reject the offending record and all following records
    • For the Read Custom, Aggregate and Join operators, there are three options.
      • Abort the dataflow
      • Skip the record containing the offending value
      • Skip the offending record and all following records

    For the output operators Write Table and Write Custom, errors are not raised by type constraints, but by the external resource or your custom coding.

    Before selecting an operator's corrective action, give some thought to the impact your choice will have on the processing.

    • Aborting the entire dataflow has a very serious impact as all processing  will immediately cease.  Records that have completed processing will  not be rolled back, processing will not continue for records that are  currently being processed, and no further records records will enter the  processing stream.
    • Skipping a record will allow processing to continue but the information  contained in the skipped record will not be processed; this may, or may  not, affect the validity of your application, which is something you  must determine.
    • Rejecting a record will also allow processing to continue but the  offending record can be captured, reanalyzed, and perhaps resubmitted  for processing.  This option may allow you to recover from the  constraint violation.
    • If you choose to skip or reject the offending record and all following  records, the records that are currently being processed will run to  completion and you may also be able to recover from the constraint  violation.
    • When it is the Aggregate operator that identifies an offending record,  you need to consider how the absence of this record will impact the  ongoing calculation.

    You may set multiple constraints for the same Atomic Type.  If you do, the constraints are evaluated in parallel.  For example, suppose a database column defined as char(15) contains social security numbers of the format 123456789 and 123-45-6789.  When these values are retrieved from the table, they will be 15 characters wide, padded on the right with space characters.  As constraints, you want to restrict the length to no more than 11 characters, limited to numeric characters and the dash.  You will use both the Maximum length and Regular expression constraints.

    constraints-1.png

    Both constraints will be evaluated.  That is, if the Regular expression pattern does not include the space character the values will fail the Regular expression test even though the Maximum length constraint will remove the space characters from the value.

    In a composite type, multiple attributes may have constraints associated with their underlying Atomic Types.  Each attribute's value must pass all its associated constraint tests or the record will fail.