Inverse Hash File Lookup?

[kadf88]

I have a flat file full of rows and a hash file that i want to do an inverse lookup against. That is, normally, a hash file servers to lookup keys but in this case I want to filter out all the rows in my flat file that do not exist in the hash file.. how do I do this without using reject rows option?

thanks

[ArndW]

You still should load you hashed file to memory and lookup each row, but instead of using a reject link just put a RefLink.NOTFOUND constraint on your output.

[kris007]

When you mean filter out, I am assuming that you want only those records in your output from your flatfile whose corressponding rows exist in the hashed file( or as you said you dont want records in your output from your flatfile which dont have a corresponding row in the hashed file). In that case, a normal lookup against the hashed file based on the key column should yield the desired results. I hope I understood your problem correctly. If not, please let me know.

Thanks
Kris

[DeepakCorning]

Add a stage variable which will act like a flag and will be set depending on the row is found in the hashed file or not and add a constraint depending on the flags value.

[kadf88]

Add a stage variable which will act like a flag and will be set depending on the row is found in the hashed file or not and add a constraint depending on the flags value.

yeah, but what kind of stage variable allows me such an ability to detect whether the row is detected or not?

[ArndW]

The one I mentioned earlier, use Reflink.NOTFOUND builtin value.

[kcbland]

See Arnd's reply, or simply check if any of the matching key column values in the hashed lookup are not null. To achieve the effect of an "inner join" you need to use a constraint that verifies if the lookup row is present. An "outer join" doesn't require a constraint, DS is ambivalent to whether the lookup exists.

[dylsing]

ArndW's suggestion will work

Assuming your lookup is called LKP, use the LKP.NOTFOUND directly in the constraint itself within the transformer, so that whatever rows are not found in the hash will be allowed to the next stage in the transformer.

Hope I made sense in that mouthful.