DSXchange

We have several cases where the results of one hash lookup are needed in order to do the next hash lookup. I know we can utilize multiple transform stages but I was wondering if anyone knew how to do this in one transform stage.

leslie

leslie,

I'll likely get clobbered for this from those who believe you should not do this, but you can indeed nest lookups within the same transformer and I have been doing so since version 3.

On the left side of the transformer in the nested lookup use a column(s) from the first lookup as the key for the nested lookup. The key derivation will continue to be red, but will compile fine.

Regards,

Mike, I sure won't clobber you, because I use that feature also.

Leslie, just keep in mind that "whatever is done before can be used after" inside the transformer.

The Transformer Stage works from top to bottom, left to right. The lookups happen in the order they appear IN the Transformer. If you need to re-arrange the lookup order, there is a handy little icon up top on the transformer that lets you re-arrange the lookup order.

-Craig

Michael

Even Ray uses this feature. Don't you Ray?

Many have posted that if Ascential wanted you to do this they would publish the feature so that it would be more visible to the developer. I spoke to an engineer while I worked for Ascential and he stated that they know this exists and there were no plans to ever disallow the functionality.

I have used it, though the trick is that you have to type it in; you can not select previous reference inputs from the "Input Column" dialog. I guess this was too hard to integrate with changeable execution order of reference input links.
In turn this means that you have to be careful with casing and spelling!

As a general rule I advise avoiding using nested lookups within one Transformer, especially now that separate Transformer stages can run in separate processes and hashed files cache can be shared between multiple processes (7.1 and later).

But I acknowledge all the comments made above; it does work, and there is tacit acceptance in Ascential Engineering that it will continue to work.

I don't type it in. I drag it to the target and then drag it back. I am not sure when you could drag from right to left from the target derivation to a lookup key field but it saves time. It is more accurate too. I am uncomfortable with it being red anyway.