QlikView Expressor: Working with Constraints

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.

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.