DSXchange

Hi Folks,

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.

Anyone has any suggesions ?.

I am stuck Its pretty urgent for me..


Thanks for your help!

IsNull() in constraint will work. Do you get any error?

As an alternative you can use Link.NOTFOUND link variable as constraint.

put this constraint on the accept link i.e. matching records with the hash file . check the reject row option for the second output link

What "doesn't seem to work" about it?

The non matching rows are not going out of the link.
Let me try the options you guys mentioned above..Will let you know shortly .

chulett wrote:What "doesn't seem to work" about it? ...

chulett wrote:What "doesn't seem to work" about it? ...

The non matching rows are not going out of the link.
Let me try the options you guys mentioned above..Will let you know shortly .

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.

I tried both of these. NOTFOUND and NOTNULL.

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?

Yes I want to look up only on this column.

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.

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.

Ok, paste the exact constrain that you have mentioned in Transformer.