Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
By continuing you indicate that you agree to Terms of Service and Privacy Policy of the site.
Informatica

What is Active Lookup Transformation

 
Updated on Sep 30, 2020

Informatica 9x allows us to configure Lookup transformation to return multiple rows. So now we can retrieve multiple rows from a lookup table thus making Lookup transformation an Active transformation type.

How to configure a Lookup as Active?

In order to use this option, while creating the transformation we must configure the Lookup transformation property "Lookup Policy on Multiple Match" to Use All Values.

It is important to note that once the lookup is created, we can no longer change the mode between passive and active anymore.

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

Implementing a Lookup As Active

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.

Go to Transformation and click Create. Select Transformation Type as Lookup and enter a name for the transformation.

Create LookUp Transformation
Create LookUp Transformation

Next check the option Return All Values on Multiple Match.

Return All Values on Multiple Match
Return All Values on Multiple Match

Here our source is the DEPT table and the EMP table is used a lookup. The lookup condition is based on the department number.

Mapping to implement Active Lookup
Mapping to implement Active Lookup
Properties Tab of Active Lookup Transformation
Properties Tab of Active Lookup Transformation
Ports Tab of Lookup Transformation
Ports Tab of Lookup Transformation

Basically we try to achive the result as the below sql select:-

SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, EMP.ENAME, EMP.SAL 
FROM DEPT LEFT OUTER JOIN EMP 
ON DEPT.DEPTNO = EMP.DEPTNO

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.
PrimeChess

PrimeChess.org

PrimeChess.org makes elite chess training accessible and affordable for everyone. For the past 6 years, we have offered free chess camps for kids in Singapore and India, and during that time, we also observed many average-rated coaches charging far too much for their services.

To change that, we assembled a team of top-rated coaches including International Masters (IM) or coaches with multiple IM or GM norms, to provide online classes starting from $50 per month (8 classes each month + 4 tournaments)

This affordability is only possible if we get more students. This is why it will be very helpful if you could please pass-on this message to others.

Exclucively For Indian Residents: 
Basic - ₹1500
Intermediate- ₹2000
Advanced - ₹2500

Top 10 Articles