Lookup Items
Lookup items allow you to use unmanaged reference data in ONE. They provide a list of predefined, formatted, and cleansed values that can be compared to raw data in order to help with data cleansing, verification, matching, and enrichment. While lookups can be used both in DQ evaluation rules and in detection rules, modifying or extending them requires advanced knowledge of the platform.
Therefore, we strongly recommend using managed reference datasets instead, which can be easily created in ONE Data through data deduplication (see Import Data from a Catalog Item, section Export deduplicated data). Additionally, using ONE Data tables enables real-time rule maintenance as any changes made to data can be automatically propagated to the relevant rule.
Lookup files can be found in Data Quality > Lookup Items.
| The following guide is intended for advanced users. |
Create lookup item
-
In Data Quality > Lookup Items select Create.
-
Provide general information about the lookup item, such as name and desrciption (optional).
Provide descriptive names, or descriptions, to make it easier to locate the lookup in MinIO. -
Select whether you want to create the lookup from a catalog item attribute, or upload a lookup file.
-
If you select Choose from Catalog Items, select the required catalog item and attribute.
You can create lookups directly from attributes, in which case this information will be pre-filled for you.
You can do this from the catalog item view from multiple tabs, including Overview, Data, and Profile & DQ insights, or from the attribute view. Use the three dots icon and select Create new lookup.
-
If you select Upload from computer, drag and drop the lookup file to the space provided, or browse files.
Bulk upload of lookup items is not supported.
-
-
In Data Configuration, define the following:
-
Rebuild strategy: Choose when the lookup data is updated.
-
NEVER: Data is not updated.
-
ON DATA CHANGE: Data is updated automatically every time after the original catalog item is updated (with a delay of several seconds).
This option is only available for ONE Data catalog items. -
SCHEDULED: Schedule regular updates for the lookup data. To enable scheduled rebuilds, first create a schedule. Use the three dots menu and select Schedule rebuild.
-
-
Duplicates handling:
-
First: One duplicated value is permitted for each value in the lookup item. The one allowed duplicate is the first occurrence of the duplicated value.
-
Omit: No duplicated values are accepted in lookup items.
-
Accept: All duplicates are accepted and there is no limit to the number of repeated values in a lookup item.
-
-
Matching values:
-
Remove accents: If selected, removes all diacritic marks (accents).
-
Remove repeated characters: If selected, removes repeated characters.
-
Squeeze whitespaces: If selected, replaces repeated white space characters with a single space character.
-
Upper case: If selected, converts all words to upper case.
-
-
Supported characters: Define the supported characters using the format
[:letter:] [:number:].If defined, characters not contained in this set are removed or substituted. By default, all characters are supported (
[:all:]). -
Substitute with: Define a string to substitute any unsupported characters, if you have defined specific supported characters in Supported Characters.
-
-
Select Save.
-
-
Select Build Lookup.
-
The lookup is built. You can now edit lookup metadata, manage access, view and restore historical versions, and edit, delete, or update the lookup.
Edit
In ONE, navigate to Knowledge Catalog > Data Quality > Lookup Items. In the three dots menu of the lookup item you want to modify, select Edit or Delete accordingly.
The editing options available depend on the lookup item type. If you created the lookup using a local file, you can only edit the general information. Otherwise, you need to upload a new lookup version.
|
Lookups can also be built as a result of rule configuration.
This happens when the user selects the System-created lookups, that is, those that were created automatically during rule implementation, are also listed on the lookups screen. It is possible to edit them but changes made here can break the rule. |
Update
| Lookup items that have been uploaded cannot be updated within ONE, only reuploaded. See Upload new version. |
Select Update lookup to rebuild lookups with the latest available data. To schedule these updates, use the three dots menu and select Schedule rebuild.
Upload new version
To upload an updated version of a lookup item:
-
Select the required item.
-
Select Upload new version.
-
Drag and drop files to the area provided, or select Browse files to use the file explorer to search for lookup files (
.lkp). -
Once the lookup has been uploaded, it is in draft state. Publish the page to complete the update.
| Lookup paths are automatically updated in Rules when the lookup is updated. |
Storage
Any lookups in ONE are part of OneFileSystem in MinIO.
Specifically, they are stored in the bucket LOOKUPS.
Lookup files in ONE Desktop
Lookup files contain reference data that can help with the cleansing, verification, matching and enrichment of records by comparing raw data records to formatted and cleansed records.
Lookup files are an integral part of data quality and can be created in ONE Desktop using the Lookup Builder step, and incorporated into plans using the Lookup Reader step.
Lookup files built using the Lookup Builder step in ONE Desktop are available in Files > data > out > [name].lkp , and can then be uploaded to ONE.
Use lookups in rules
While lookups can be built as a result of rule creation (when using the is from catalog item and is not from catalog item options), they can also be used in rule implementation.
To utilize lookups in rule conditions, use the options is from lookup and is not from lookup.
|
If you are using Advanced Expression and not Condition Builder, use the syntax Alternatively, use the |
Was this page useful?