1

I have a problem with the dynamic lookup of the Informatica. My new lookup row is only returning 0 (duplicate) and 1 (Insert) it never returns 2(update). When i added a new row with the same natural key and update a column in a row the lookup returns 1 rather 2 which is update!

apoellitsi
  • 249
  • 6
  • 21
  • Can you please add some details? One quick thought I have is that length of one of the ports is not enough, e.g. if you use string(5) to compare 'abcdefg' would result in 'abcdefg' != 'abcde' thus returning a NewLookupRow as 1. – Maciejg Feb 14 '14 at 18:55
  • hello i tried what you said but still i have the same problem! Do i have to check all the ports of lookup up table? I have a look up table with 5 ports and i compare only the 3 of them. – apoellitsi Feb 18 '14 at 08:22
  • now is working but i have another problem. Lets say i add another row with the same key but i update the description to my table. It shows number 2 correctly but it shows the old row with number two rather 0 which is duplicate. – apoellitsi Feb 18 '14 at 09:30

2 Answers2

0

You have (at least) of things to look for:

  1. Check in the property label of the lookup transformation that the "Insert Else Updated" and "Update else insert" are checked (they are not by default).
  2. Check also the port that could cause an update are correctly associated (in the port label) and have not the "Ignore in comparison" flag checked.
momobo
  • 1,755
  • 1
  • 14
  • 19
  • the label property is checked. Now the ignore in comparison flag is checked and i can not unchecked it. it doesnt let me! – apoellitsi Feb 18 '14 at 08:23
  • now is working but i have another problem. Lets say i add another row with the same key but i update the description to my table. It shows number 2 correctly but it shows the old row with number two rather 0 which is duplicate. – apoellitsi Feb 18 '14 at 09:31
  • The comparison should be on the business key. Try to read this tutorial: http://www.dwbiconcepts.com/etl/14-etl-informatica/16-dynamic-lookup-cache.html – momobo Feb 18 '14 at 11:49
0

Okay i solved it! The problem is that you need to sort your table ports before creating the dynamic lookup!

apoellitsi
  • 249
  • 6
  • 21
  • Are you sure that was this that solved your problem? The port order should not have any effect. – momobo Feb 20 '14 at 07:31