Joining to Hashed File using "like" or "conta

tracy · Post by **tracy** » Wed Nov 04, 2009 8:31 pm

I've got a Transformer stage that has an incoming Sequential file and an incoming Hashed file.

For instance, let's suppose my Sequential File has the following data:

Product|Quantity
AA1234|10
AA3423|50
AB3988|44
AB3099|30

My hashed file may have this:

Product|Flag
AA|Y
AB3|N

I want my output to be:

Product|Quantity|Flag
AA1234|10Y
AA3423|50Y
AB3988|44N
AB3099|30N

So I want to join to the Hashed File using only the first few characters. But as you can see, I don't know exactly how many characters (sometimes 2 characters, sometimes the first 3).

I'm having trouble figuring this out. I can't put something like this in the Hashed File Key Expression because it won't let me access the Hashed File Values itself: SequentialFile.Product[1,Len(Hashed.Product)]

Any suggestions?

chulett · Post by **chulett** » Wed Nov 04, 2009 8:51 pm

Hashed lookups are equi-joins, so no 'like' or 'contains'. If it's always 2 or 3 characters, however, do two lookups to the same hashed file, one with the first two and the other with the first three characters and then take whichever one succeeds. Define a priority order if there is a possibility for both to match.

tracy · Post by **tracy** » Thu Nov 05, 2009 10:29 am

I was afraid of that. Thanks for the response. At least I can stop trying to do it more cleanly now.

chulett · Post by **chulett** » Thu Nov 05, 2009 10:44 am

For (perhaps) 'more cleanly' you could put a UV stage over the hashed file (which would require a VOC record if pathed) and then use 'normal' SQL for the lookup.

tracy · Post by **tracy** » Thu Nov 05, 2009 11:03 am

Actually, once I gave up on that method and was able to start thinking of alternatives, I decided to do it with SQL in the After SQL tab after I load the data into the database.

I'm doing something like this (where SEQUENTIALFILE and HASHEDFILE are now used as Oracle tables instead of DataStage objects):

update SEQUENTIALFILE s set
s.FLAG = (select h.FLAG from HASHEDFILE m where s.PRODUCT like h.PRODUCT);

I'm still coding the entire solution, but so far this seems to be doing the trick, though note that my HASHEDFILE data now looks like this:

Product|Flag
AA%|Y
AB3%|N

Thanks again for the feedback.