Unable to load distinct records via lookup in informatica

Question

This is a very strange thing happening i dont know why. I have created a mapping that transforms the data via expression and loads the data into the target(file) based on lookup on the same target.

Source table

#CompanyName
Acne Lmtd
Acne Ltd
N/A
None 
Abc Ltd
Abc Ltd
X

Mapping

  Source
->Exp(trim..)
->Lookup(source.company_name
  = tgt.company_name)
   ReturnPort is CompId

  -> filter(ISNULL(CompId)) 
  -> Target 
    Compid (via sequence 
     gen) 
     CompName 

The above mapping logic inserts duplicate companynames as well like in source 2 Abc Ltd records same is repeated in target as well. I dont know why. I have tried to debug as well the condition evaluates to true in filter that companyid is null even if the record is already inserted in target.

Also, i thought it might be the case of lookup cache i do enabled dynamic as well but same result. It should have worked like an sql query

select company_id
From lkptarget where 
company_name
In (select company_name 
 from 
Source)

Therefore, for Abc Ltd the filter condition should have result in false

  Isnull(company_id) false

But, this is getting true. How do I get unique records via lookup and without using distinct?

Note: lookup used is dynamic lookup already

Answer 1

That was in fact a dynamic cache issue the newLookupRow gets assigned a value of 0 on duplicates so I have added the condition in filter as ISNULL(COMPANYID) AND NEWLOOKUPROW=1 and finally that did work.