What are the different types of lookups in Astera Centerprise?¶
There are four types of lookups in Astera Centerprise: List lookup, File Lookup, Database Lookup and SQL Statement Lookup.
The List lookup is the type of lookup that stores information in the metadata which means that the lookup information is stored within the dataflow itself. You can define a list of source as well as lookup values in the lookup properties, which are then looked up and replaced when you run your dataflow.
The File lookup is used to lookup values from an Excel or Delimited file. It can be performed based on a single lookup field as well as a combination of fields. Similarly, a file lookup 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.
The Database lookup is used to lookup values in a database table that contains lookup values as well as the output values.
The DB lookup can be performed on a single lookup field or a combination of fields. Similarly, it can return a single lookup field or a combination of them in the output. In either case, the output field or fields are returned from the records in which the lookup values match the incoming values.
The SQL Statement Lookup works just like a Database Lookup. However, unlike Database Lookup in which we use a database table to look up matching values, we run an SQL query to fetch matching records from the lookup table. We can define one or more conditions by specifying the relationship between the database element and the input element which should be fulfilled by all the incoming records. Any record which does not pass the defined condition(s) would be discarded.