I am stuck on an issue. I have to carry forward rows that do not match the look up with the hashed file.
For eg : Input link L1 has columns C1,C2,C3
Hashed file link L2 has column C1
I do a look up on C1 on both and basically want to carry forward the rows that do not match in hashed file. I did isnull(L2.C1)=1 on the constraint to carry forward the rows that do not match. But it doesnt seem to work.
I have 3 rows that doesnt match the look up and it gets inserted into table.
FYI the look up is the same table I insert into.
Then again I run the job which again inserts the 3 rows once more. The column I am looking at is not a key column. But i do not want this to get inserted if the row is already inserted. I dont know whats wrong.
I tried NOTFOUND option as well. Its also giving me the same result.
A lookup is an equi-join style. Remove any contraint. All primary input rows will go to the output link. The lookup result doesn't matter, it never has any bearing on whether a row is output.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
The strange thing is first time when i do not have those matching records in table it correctly identifies it as a nonmatch and inserts.
Second time when i run the job with the rows already present in the table, with the NOTFOUND condition on constraint, it again inserts it into the table .Huh???.
I checked the look up table from view data and this column value exists. Also in the input link. It means the look up is happening. But why it is going to the NOTFOUND link??..
Any ideas??.
balajisr wrote:IsNull() in constraint will work. Do you get any error?
As an alternative you can use Link.NOTFOUND link variable as constraint.
Do you mean that ther is no way to capture non matching records?.
Thats exactly what I want.
kcbland wrote:A lookup is an equi-join style. Remove any contraint. All primary input rows will go to the output link. The lookup result doesn't matter, it never has any bearing on whether a row is output. ...
The condition given might not been correct. Is the NotFound the only condition you have in transformer? And C1 is the only field you are looking up after?
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
kumar_s wrote:The condition given might not been correct. Is the NotFound the only condition you have in transformer? And C1 is the only field you are looking up after? ...
Does the Datatype and the data match happens properly? Have you taken care of the whitespaces on leading and trailing end of both the stream.
On second time, try to pass it on to a Sequential file for testing purpose and check what data you getting from the lookup actually.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
kumar_s wrote:Does the Datatype and the data match happens properly? Have you taken care of the whitespaces on leading and trailing end of both the stream.
On second time, try to pass it on to a Sequential file ...
Yes both streams are integer 22.
In sequenctial file too I get the same value for C1.
Its pretty urgent for me.. 
