Lookup Technique Version 4.6.6.6


In the transformation discussed in the previous video, we encountered three rows corresponding to categories. However, it's essential to generate a unique code for each of these categories at the source. Therefore, our initial step involves creating a column to accommodate these codes.



To create these codes, we need to reference the destination table and ensure that the codes we generate do not already exist. As the destination table is currently empty, we can proceed with assigning codes sequentially based on the number of existing rows.


For example, when processing the first row from the source table (e.g., "mineral waters"), we check if this text exists in the destination table. Since the destination table is empty, "mineral waters" does not exist, and we assign the code 1.



However, if we encounter duplicate categories or new categories in subsequent iterations, we must ensure that we assign unique codes. This involves checking against the existing codes in the destination table and incrementing the sequence accordingly.


The Lookup technique automates this process, ensuring that each category is assigned a unique code based on its existence in the destination table.


To implement the Lookup process, we first create a container and a transformation associated with the destination table. This transformation serves as a reference for the Lookup process. Then, in the Lookup transformation dialog box, we specify the parameters, including the search fields and criteria for assigning codes.











Once configured, the Lookup process automatically generates codes for categories, ensuring consistency and uniqueness in the destination table.