How to perform a hashed file lookup

[jamesrender]

Apologies for such a beginners question, no DS experience:

How do I perform a hashed file lookup. Specifically how do I set up my constraints..

I've created a transformer stage with two inputs. The format of both inputs is the same, one is my hashed file for lookup (lkReferenceLookup), the other comes from a previous stage with my data (lkValidRecords)

I've got two decimal columns, both columns are keys.

when the lookup doesn't succeed I want to send it out to my oracle link lkNewRecords, and went it does to a sequential file of duplicates, lkDuplicates.

in the transformer stage, I dragged both the columns from the lkValidRecords to the lkReferenceLookup, connecting the two.

(I read something about multi-column lookup, but I couldn't get any checkbox option to appear)

Then I dragged the columns from my input src (lkValidRecords) to both of the outputs.

This was sending all records down both branches. I read a post that I need to set up some constraints to prevent

I'm not sure whether the lookup is actually taking place... is there a way that I can check this.

so I have created two stage variables DuplicateRecord, NewRecord. I wrote expressions that looked like this

(lkValidRecords.column_1 = lkReferenceLookup.column_1) and (lkValidRecords.column_2 = lkReferenceLookup.column_2)

replacing = with NE for the reverse.

I used these as constraints on the outputs

This meant that nothing was sent down either branch.

Finally I tried. lkReferenceLookup.NOTFOUND on one of my stage variables and this made the hang..

thanks

[jamesrender]

i've changed the constraints on my outputs to lkReferenceLookup.NOTFOUND and not(lkReferenceLookup.NOTFOUND), but its sending the wrong record to the wrong output and its hangs after the ora-0001

[jamesrender]

to me it looks like its not doing the lookup

all my records are being sent down the didn't find a match link, I've viewed the job in designer and once I@ve run the job there it reports that 0 rows were looked up.

I've viewed the hashed file and the records are in there.. am just wondering if I need to define multirow lookup...

maybe its not possible to have both columns specified as the lookup key in a hashed file...

[gpatton]

yes you can have two key colums in lookup.

1) make sure the column definitions are the same for the source and hash file.

2) try If ( IsNull(lkReferenceLookup.column_1) or IsNull(lkReferenceLookup.column_2)) as the constraint for the failed lookup stream.