Informatica - How do you identify existing rows of data in the target table using lookup transformation?

GeekInterview.com > Interview Questions > Data Warehousing > Informatica

Go To First | Previous Question | Next Question

Informatica | Question 3 of 647

How do you identify existing rows of data in the target table using lookup transformation?

Can identify existing rows of data using unconnected lookup transformation.

Total Answers and Comments: 6

Last Update: June 10, 2009

Sponsored Links

Best Rated Answer

—

Submitted by: SK

There are two ways to lookup the target table to verify a row exists or not :
1. Use connect dynamic cache lookup and then check the values of NewLookuprow Output port to decide whether the incoming record already exists in the table / cache or not.

2. Use Unconnected lookup and call it from an expression trasformation and check the Lookup condition port value (Null/ Not Null) to decide whether the incoming record already exists in the table or not.

Above answer was rated as good by the following members:
vivek1708, aniban, phdsong, ctduffey, ravi8485, nitijosh, neha.rakesh30, joyfun23

September 12, 2005 11:09:16

Praveen Vasudev

RE:

You with can use a Connected Lookup with dynamic cache on the target.

Is this answer useful? Yes | No

June 13, 2007 08:02:05

Narla

RE:

Look Up is used to verify whether the data exists or not. Then what does your Question mean

Is this answer useful? Yes | No

August 09, 2007 15:39:06

Gaurav Gupta

RE:

look up the target table rows in your mapping and then compare all the attributes with the current source row in an expression transformation using if else and update or insert accordingly

Is this answer useful? Yes | No

August 30, 2007 05:01:26

RE:

There are two ways to lookup the target table to verify a row exists or not :
1. Use connect dynamic cache lookup and then check the values of NewLookuprow Output port to decide whether the incoming record already exists in the table / cache or not.

2. Use Unconnected lookup and call it from an expression trasformation and check the Lookup condition port value (Null/ Not Null) to decide whether the incoming record already exists in the table or not.

Is this answer useful? Yes | No

7 0

Overall Rating: +7

May 08, 2008 00:07:15

vivek1708

Member Since: May 2008 Contribution: 18

RE: How do you identify existing rows of data in the target table using lookup transformation?

2 ways:
1) Using an unconnected lookup and calling the same from an expression. based on the output the particular set of rows can be filtered out and not be a part of target load data by putting a filter right after the expression.
2) using a connected lookup before the target and followed by an update strategy.
This lookup has to be a dynamic lookup (please be careful while using dynamic lookups as the keys being used in the target table need to be clearly specified) and based on the value of the NewLookupRow column value - we can do a DD_UPDATE DD_INSERT or DD_REJECT.

Is this answer useful? Yes | No

2 0

Overall Rating: +2

June 09, 2009 13:38:40

sppavankumar

Member Since: May 2009 Contribution: 3

RE: How do you identify existing rows of data in the target table using lookup transformation?

Set a default value in the lookup transformation as '-1' or '-2'. So if the incoming row is present in the target it will pass the row with the respective column value which is looked upon or else if the value is not present in the target it will populate the particular column with '-1' or '-2'. So the '-1's and '-2's are the rows which WERE not existing in the Target table before.

Knowing the non-existing rows we can come to know about the existing rows.

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank