Why does the Return All option on the Lookup Options screen in the File Lookup transformation give an error?

The error is due to a mismatch in the cardinality of the two incoming data sources. When we select Return All in the Lookup Options screen, it returns multiple values to write in one field, leading to a one-to-many relationship. The change in cardinality returns an error.

To resolve this problem, flatten the data using some other transformation, such as Join or Union, before writing it to a destination.