List Lookup is a type of lookup that stores information in the metadata. Which means that your lookup data is stored in the dataflow itself. List Lookup uses a list of values for both the input and output fields. You can use it to look up for certain values in your source data and replace them with the desired information. Or you can define a list of values in the lookup grid in properties, and the value is then looked up in the grid when you run your dataflow.
Let’s see how it works in Centerprise.
Steps to Use the List Lookup in Centerprise
- Select your source by dragging the relevant object from the Sources section in the Toolbox on to the dataflow designer and provide the file path to retrieve your data.
(Note: In this example, we are working with a Fixed-Length File Source that contains customer information for a fictitious organization, but you can select the source type from a wide range of options provided in Centerprise. Click here to learn about setting up sources).
If we preview this data, you can see that this record contains information about customers from different countries. If we want to convert these records into CountryCodes, we can do that using List Lookup.
2. Drag the List Lookup object from the transformations section of the toolbox and drop it onto the designer.
This is what a List Lookup object looks like:
3. Now map the field from the source dataset you want to look up values for to the ‘value’ field in the List Lookup object.
4. Right-click on the List Lookup object and select Properties from the context menu.
On this List Lookup Map Properties screen, the first option we have is the Case Sensitive Lookup checkbox which is already checked by default. When this option is checked, the List Lookup will look up for values on a case sensitive basis. If you don’t want to perform look up on case sensitive basis, you can uncheck this option.
Next, you can see that there is a grid or a table where we specify the source value and the destination value. Source values are the values from your source data, and destination values are the values you want to replace with the source values.
For example, when we type our source value Germany in the Source Value section and the Destination Value as DE, Centerprise will convert the source value into destination value in the output.
This is one way of specifying the lookup values. However, there can be a lot of source values and typing them manually can be a tedious task. There’s a more efficient way of doing this in Centerprise.
If you right-click on the List Lookup object, you can see that there’s this option “Fill Lookup List with Unique Input Values.”
When you click this option, the List Lookup will prepopulate the source values in the Source Value column. If you go to the List Lookup Properties, you can see that it has prefilled this table with unique source values.
Now, all you have to do is type in the Destination Values, that is, the codes corresponding to each country name.
5. Once you have populated the lookup list, click Next to proceed to the next screen. Here you can see some lookup options.
6. In case the lookup field does not return any value for a source value, then you have to select from the following option:
- No Message – won’t mark the unmatched source value as an error or warning
- Add Error – the List Lookup table will trigger an error for the records that found no match in the lookup field
- Add Warning – the List Lookup will generate a warning and return a null value for records from the source that do not have any match in the lookup table
7. In addition to that, when the value is not found in the lookup list, you can choose from the following options to assign it a value:
- Assign Source Value – will return the original value from the source
- Assign Null – will return a null value for each unmatched source record
- This Value – you can type in a specific value in the given field, and the List Lookup will return the same value for each unmatched source value
In this example, I want to add an error and return the source value if the lookup value isn’t found, so I’ll select the ‘Add Error’ and ‘Assign Source Value’ options. You can choose your preferred option and click Okay.
8. Now, if we preview the output, you can see that for each country name from my source, the List Lookup has returned a corresponding code value.
9. These CountryCodes will flow through this output port if you want to write your data to a destination.
10. This is how we can map the lookup values to a target or a transformation in the dataflow using the output port.
This concludes using the List Lookup transformation in Centerprise. Leave a thumbs up if you found this article helpful. If you have any queries, feel free to reach out by dropping a comment below, and we will get back to you.
0 Comments