List Lookup Transformation

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.

Use Case

In this example, we are working with a Fixed-Length File Source that contains customer information for a fictitious organization. The customers data contains information about customers belonging to different countries. We want to convert the country names in this data into CountryCodes by using List Lookup transformation.

How to Work with List Lookup in Centerprise

1. Select source from the Sources section in the toolbox and drag-and-drop it onto the designer window. In this case, a Fixed-Length File Source is used.

1

To preview the incoming data, right-click on the source object’s header and select Preview Output.

2

2. To start working, drag-and-drop the List Lookup object from Toolbox>Transformations>List Lookup.

3

This is what a List Lookup object looks like:

4

3. Map the field from the source dataset you want to look up values for to the ‘Value’ field in the List Lookup object.

5

4. Now, right-click on the List Lookup object and select Properties from the context menu. List Lookup Map Properties screen will open.

Here, the first option we have is the Case Sensitive Lookup checkbox which is always 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.

7

Next, you can see that there is a table where we can specify the source value and the destination value. Source values are the values from your source data, and destination values are the values which you want to replace with the source values.

6

For example, if we write destination value ‘DE’ against source value ‘Germany’, Centerprise will write ‘DE’ in place of ‘Germany’ in the output.

8

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 is an option called Fill Lookup List with Unique Input Values.

9

Selecting this option prepopulates the source values in the Source Value column with unique source values.

16

Now, all you have to do is type in the Destination Values, that is, the codes corresponding to each country name.

10

5. Once you have populated the lookup list, click Next to proceed to the Lookup Options screen.

11

6. In case, the lookup field does not return any value for a given 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

17

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.

13

In this example, I want to add an error and return the source value if the lookup value isn’t found, so let’s select the ‘Add Error’ and ‘Assign Source Value’ options. You can choose your preferred option and click Okay.

11

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.18

9. These CountryCodes will flow through this output port if you want to write your data to a destination.

14

10. This is how we can map the lookup values to a target or a transformation in the dataflow using the output port.

15

This concludes using the List Lookup transformation in Astera Centerprise.