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.