Lookup Policy on Multiple Match

In our Daily Business scenarios, We come across situations like  we require  Lookup transformation to retun all the values those met the Lookup condition.
In the previous versions of  Informatica we don't have this facility as the options available under
Lookup Policy on Multiple Match are 4 only.




1.Use First
2.Use Last
3.Report Error
4.Use Any Value

Then, how we can configure Lookup to return Multtple Matching values.?
From Informatica 9.x onwards we can retrieve multiple rows from a lookup table thus making Lookup transformation an Active transformation.
Even from Informatica 9.x onwards you can avail this facility by making samll activity which I describe here

How to configure a Lookup as Active? 

In order to configure lookup  transformation to return multiple rows we must configure the Lookup transformation property "Lookup Policy on Multiple Match" to Use All Values.

Whenever the Lookup policy on multiple match attribute is set to Use All Values, the property becomes read-only afterwards and the lookup becomes Active transformation.

consider the below scenario:

Suppose we have customer order data in a relational table. Each customer has multiple orders in the table. We can configure the Lookup transformation to return all the orders placed by a customer.
Now check the below simple mapping where we want to return all employees in the departments.
1.Go to Transformation and click Create.
2.Select Transformation Type as Lookup and enter a name for the transformation.
3.Select the Lookup table from  tables under tabs Source/Target.
4.check-in the chek box "Return All Values on Multiple Match" at the bottom of the window.
5.click OK.




Finished...! You configured Lookup  as Active transformation.
Now double click on Lookup transformation and goto Properties tab. Here you can observe that
Lookup policy on multiple match is set to Use All Values and it is Read Only.

Active Lookup Transformation Restrictions:

1. We cannot return multiple rows from an unconnected Lookup transformation
2. We cannot enable dynamic cache for a Active Lookup transformation.
3. Active Lookup Transformation that returns multiple rows cannot share a cache with a similar Passive Lookup Transformation that returns one matching row for each input row.

Hope this helps.