File Lookup Transformation

The File Lookup Transformation object in the Astera Data Stack is used to look up values coming from a source. It uses an Excel or delimited file that contains the lookup values as well as the output values to perform the lookup.

File lookup can be performed based on a single lookup field as well as a combination of fields. Similarly, a File Lookup Transformation object can return a single output field from a lookup table or a combination of fields. In either case, the output field or fields are returned from the records in which the lookup values match the incoming values.

Use Case

In this scenario, we have a Customers dataset from a fictitious organization stored in a database source. It contains information about customers from different countries.

We want to replace the country names with country codes in our database, by switching them with the lookup values (country codes) stored in an Excel file. To achieve this, we will use the File Lookup Transformation object.

Working with the File Lookup transformation

1. Select the relevant source object from the Sources section in the Toolbox. In this example, we will use the Customers data stored in a database table.

Right-click on the source object’s header and select Preview Output. You can see the country names in the Country field which we want to convert into country codes.

1. Drag and drop the File Lookup transformation object from Toolbox> Transformations> File Lookup onto the dataflow designer.

Now, let’s configure the transformation object.

1. Right-click on the header of the File Lookup transformation object and select Properties.

1. A File Lookup Map Properties window will open where you can see an option for Source File Type. Here, you need to specify the type of your lookup file.

You can perform the task using an Excel or Delimited lookup file.

1. Select the Source File Type from the dropdown menu. In this example, our country codes are stored in an Excel file so we will specify the Source File Type as Excel.

1. Click Next to proceed to the File Lookup Details window.

File Location: Here, you need to specify the File Path to the lookup source file.

You will see two options:

• First Row Contains Header: You can check this option if your lookup file contains a header in the first row. Otherwise, you can leave it unchecked.

• Worksheet: If your lookup file contains multiple worksheets, you can select the worksheet you want to use to perform the lookup.

1. Click Next to proceed to the Layout Builder. Here, you can make changes to the object’s layout by modifying the existing fields or creating new fields.

Once you are done, click Next.

1. On the next window, you will see various Lookup Options.

If multiple values are found, then opt for the Multiple Matches Found Option.

• Multiple Matches Found Option: This option provides the flexibility to choose the output value if more than one match is found for a single value in your lookup file. The option expands into a drop-down list where you can select one from the following three options:

  • Return First: Will return the first matched value found.

  • Return Last: Will return the last value among all the matched values.

  • Return All: Will return all the values in the lookup file that match a source value.

• If Value Is Not Found In The Lookup List: In case no lookup value is found against a source value, you can choose one of the following three options to be appended to your output.

  • No Message: There will be no message and the output will be the same as the input value.

  • Add Error: An error message will appear with the output.

  • Add Warning: A warning will appear with the output.

• If Value Is Not Found In The Lookup List, Assign Value: If no lookup value is found against a source value, you can assign an output value of your choice.

  • Assign Source Value: Will return the source value in the output.

  • Assign Null: Will assign null to your output value.

  • This Value: You can select this option and assign any value of your choice.

1. Click Next. This will take you to the Config Parameters window, where you can further configure and define parameters for the File Lookup transformation object.

Once you have configured the File Lookup transformation object, click OK.

1. Map the Country field from the Source object to the Country field in the File Lookup transformation object. Now map the Code field from the transformation object to the Country field in the Database Table Destination object.

This is what your dataflow will look like:

1. Map the remaining fields from the source object to the destination object.

1. Right-click on the destination object’s header and select Preview Output.

1. You can see that the country names in the database table have been successfully converted into country codes.

This concludes using the File Lookup Transformation object in Astera Data Stack.