Carry forward rows that do not match in Hashed file
Moderators: chulett, rschirm, roy
Carry forward rows that do not match in Hashed file
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!
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!
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
Code: Select all
NOT(ReferenceLink.NOTFOUND)
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.
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
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
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??.
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.
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. ...
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.
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.