DSXchange

I am trying to use a hashed file to lookup based on 4 fields - 3 of which are on equality and th 4th one on inequality condition. I move the record out when the lookup is not found.

For example, The lookup looks like

Source.Field_1 = Lookup.Field_1 and Source.Field_2 = Lookup.Field_2 and Source.Field_3 = Lookup.Field_3 and Source.Field_4 <> Lookup.Field_4

Any help how I can do that will be appreciated.

Thanks.

Bhusan

SonShe wrote: For example, The lookup looks like

Source.Field_1 = Lookup.Field_1 and Source.Field_2 = Lookup.Field_2 and Source.Field_3 = Lookup.Field_3 and Source.Field_4 <> Lookup.Field_4

hi Bhushan,
1. Perform only the equality lookups (Fields 1 through 3).
2. Pass Source.Field_4 and Lookup.Field_4 to the output link.
3. In the output link constraint, use the constraint Source.Field_4 <> Lookup.Field_4.

gateleys

Hi Gateley,

Thanks for the reply. I believe I cannot do that since dropping the 4th field from the key of the hashed file will cause data loss. I thought about this already.

Thanks.

Load your source in a staging table and pass a sql join. That will be the most feasible.

SonShe wrote:Hi Gateley,

Thanks for the reply. I believe I cannot do that since dropping the 4th field from the key of the hashed file will cause data loss. I thought about this already.

Thanks.

Since hashed file is fundamentally based on equality matches, you will have to find a work around.
Another method
==========
Use a UV stage pointing to the hashed file. This way, you can use the hashed file like a database table, and in the reference SQL, use -
Of course, the Fields here refere to the hashed file key fields. You will need to index the fields1, 2, 3 and 4. Then build the indexes.

Yet another method
===============
Try loading the lookup data into some work table (instead of hashed file). If the lookup is relatively small, use the table for direct lookup. Else, you will need to land your source rows into another table. Then perform the necessary join between these 2 work tables.

gateleys

DSguru2B wrote:Load your source in a staging table and pass a sql join. That will be the most feasible.

Like I pointed out in my 'yet another method'.

gateleys

I beat you to it, gateleys

DSguru2B wrote:I beat you to it, gateleys

Hey DSGuru,
I am no match to you....... you know that.

gateleys

Naa, It was a lucky stroke of the "Submit" key.
Welcome back. Good to see posts from you. Keep it up.