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?
Joining to Hashed File using "like" or "conta
Moderators: chulett, rschirm, roy
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.