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.
  • Interview Candidate
  • 25
  • 22163

Editorial / Best Answer

Answered by: SK

  • Aug 30th, 2007

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.

Showing Answers 1 - 25 of 25 Answers

Praveen Vasudev

  • Sep 12th, 2005
 

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

  Was this answer useful?  Yes

Narla

  • Jun 13th, 2007
 

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

  Was this answer useful?  Yes

Gaurav Gupta

  • Aug 9th, 2007
 

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

  Was this answer useful?  Yes

SK

  • Aug 30th, 2007
 

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.

vivek1708

  • May 8th, 2008
 

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.

sppavankumar

  • Jun 9th, 2009
 

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.

  Was this answer useful?  Yes

doppalpaudi

  • Jul 9th, 2010
 

Lookup Transformation is used to cheek weather the data is present in target or not.
This transformation is of 2 types

1. Connected lookup transformation
2. Unconnected lookup transformation.

By using above both we can cheek the target for data .

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions