Validating Data Using Table Range - trillium_discovery - trillium_quality - Latest

Trillium Control Center

Product type
Software
Portfolio
Verify
Product family
Trillium
Product
Trillium > Trillium Discovery
Version
Latest
Language
English
Product name
Trillium Quality and Discovery
Title
Trillium Control Center
Copyright
2024
First publish date
2008
Last updated
2024-10-18
Published on
2024-10-18T15:02:04.502478

The Table Range function in the Transformer allows you to validate data by performing a look-up on up to five attributes, followed by a range check on an attribute if the look-up is successful. You can also recode up to five attributes if both the look-up and the range check are successful. Recoding is optional.

Example

Say you have insurance claim data that includes record number, date of loss, claim type and postal code. You have a predefined insurance table that lists start/end dates, claim type, postal code and event. You want to match up the claim data against the insurance table and verify if the dates for particular type of claim type and postal code fall within the valid range.

Claim data (input)

Record Number Date of Loss Claim Type Postal Code
1 01/01/2012 Commercial 01821
2 01/03/2012 Auto glass 01821

Insurance table

Start Date End Date Claim Type Postal Code Event
01/01/2012 01/01/2012 Commercial 01820 Hurricane
01/01/2012 01/01/2012 Commercial 01820 Flooding
01/02/2012 01/03/2012 Commercial 01821 Flooding
01/03/2012 01/04/2012 Auto glass 01821 Hail storm

Using the table range function, you can check if [Claim type + Postal code] equals the valid Date of Loss by looking up the Insurance table. Record 1 will not be valid because the date (01/01/2012) for [Commercial + 01821] is out of range on the 3rd row in the table. Record 2 will be valid because the date (01/03/2012) for [Auto glass+ 01821] is within the range on the 4th row in the table.

Guidelines

Note the following:

  • You need a previously created lookup table and ddx file in the server or client.
  • You can lookup attributes using literal value (The actual value. Example: Value = SW10 8BP) or mask values (The shape of the value. Example: Mask = AANN NAA).
  • Values in the table can be either strings (for example, DD/MM/YYYY or YYYY/MM/DD) or numbers. The numbers can come from using the DATE_TO_SECOND function in the Expression Builder.
  • You can use any encoding for the strings. Make sure that the attributes for output range, start range, and end range are all the same type and encoding.