Cascading Lookups

sdfasih · Post by **sdfasih** » Mon Mar 12, 2007 8:40 pm

Hi,
I was wondering if there is a way to do this.I would like to know if I have three Hash Files each having a single column say Col1.
Hash1 Col1 is Source to transformer stage
Hash2 Col1 is lookup to transformer stage
Hash3 Col1 is lookup to transformer stage.
I can do a lookup Hash1 Col1 with Hash2 Col1.Now can I do a lookup Hash2 Col1 with Hash3 Col1?
Thanx in advance.

kumar_s · Post by **kumar_s** » Mon Mar 12, 2007 8:58 pm

If the Hash2 and Hash3 has only one column there is no point in doing lookup. And more over, if you are doing a lookup in Hash1 colum1 with Hash2 colum2, you are determining Hash1 as primary stream. So it should be same if you do a Hash2 col1 or Hash1 col1 with Hash3 Col1.
If this doens't get you clarity, pls explain the functionality of your requirement with more example.

kumar_s · Post by **kumar_s** » Mon Mar 12, 2007 9:00 pm

Thanks for posting in Server forum, Would you mind deleting the orphan post left out in PX, before some one updates it. You can't delete the the one I had updated though.

loveojha2 · Post by **loveojha2** » Tue Mar 13, 2007 2:38 am

Looking at the topic heading Cascading lookup.

I guess you mean something like
Src Col1 to be Lookuped to Col1 (hash1)
Col2 (hash1) to be lookuped to Col2(hash2)

Cascading lookup is possible in the data stage, could be done in two ways
1st: In One transformer
In TX1 Src Col1 to Hash1 Col1
and Hash Col2 to Hash2 Col2
Doing it this way works fine (compile+run) but it shows the derivation in the second lookup column in red (may be its corrected in the latest versions)
2nd: In two transformer one after the other
In TX1 Src Col1 to Hash1 Col1
In TX2 Src+hash1 Col2 to Hash2 Col2

skumar · Post by **skumar** » Tue Mar 13, 2007 6:15 am

Hi Love,

I tried the first method in your two explanations....I have a doubt here as you told that to keep the link withTX1 Src Col1 to Hash1 Col1 and Hash Col2 to Hash2 Col2. can we put a lookup with the Lookup (Reference link???)Because i tried it with a small example as i never worked on cascading Lookups it was not letting me to pull the Hash1 Col2 to Hash2 Col2 .If you don't mind can you please explain a bit more clear about this......

Cascading lookup is possible in the data stage, could be done in two ways
1st: In One transformer
In TX1 Src Col1 to Hash1 Col1
and Hash Col2 to Hash2 Col2
Doing it this way works fine (compile+run) but it shows the derivation in the second lookup column in red (may be its corrected in the latest versions)

Regards,
skumar.

kumar_s · Post by **kumar_s** » Tue Mar 13, 2007 6:22 am

Lookup can be done only to Main stream to Lookup stream. So to hashed file need to be looked up, it need to be part of Main stream.
In first lookup based on key, drag the column that need to be looked up in concecutive stages in to main stream. So that now it will be part of the main stream. And you can do the look in the Hash2 or what ever.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 13, 2007 6:30 am

Cascading lookup CAN be done.

But you can not construct it with the GUI; on the second and subsequent reference key expressions you must type in the qualified name of the return column from the earlier reference input link.

It will remain red, but it will compile and it will work.