DSXchange

I need to do multiple lookups for different columns in the source against the same dataset. I cannot use the Range lookup since the values are not necessarily in a certain range.

Do I have to create multiple datasets for each lookup? Or, are there any better options that I can avoid creating multiple datasets?
Thanks.

You need to use 'multiple lookup' stages not 'multiple datasets'.

Regards
Sreeni

Sreenivasulu wrote:You need to use 'multiple lookup' stages not 'multiple datasets'.

Regards
Sreeni

Yes, that is certainly an option but then I am just replacing creating multiple datasets with as many lookup stages. Do you think there is some other options to avoid this? Thanks.

As far as i know there are no other options

Regards
Sreeni

No need to create multiple datasets, just multiple lookups to the same dataset would suffice.

why are you creating multiple datasets. Just use the single dataset for all of your lookup's.

1. If doing multiple lookup's in the same job then use the copy stage.
2. If doing lookup's in separate job's then no need to create copy of dataset. Use the original one.

One data set will suffice. Each reference input to your Lookup stage creates a separate virtual Data Set into which the reference data are read and build an index on the identified key (the LUT_CreateOp operator). So you do actually have separate data sets at run time - you don't have to design this feature.

speedsterrules wrote: Yes, that is certainly an option but then I am just replacing creating multiple datasets with as many lookup stages. Do you think there is some other options to avoid this? Thanks.

Use a Transformer in between the Lookup stage and dataset stage
You will get multiple copies of the data or else even you can just map the particular dataset column to multiple columns via transformer and achieve this.

Please post more info (if there is any) about the design which may help our experts to suggest better.

That strikes me as overkill. You can have as many connections to the same Data Set as you require. Ultimately (with either approach) you get N virtual Data Sets for N reference inputs. I don't think the Transformer stage is going to give you anything. If you want to drop columns differently, use Copy stages on each of the reference inputs to the Lookup stage - a far lower cost than Transformer.

ray.wurlod wrote:That strikes me as overkill. You can have as many connections to the same Data Set as you require. Ultimately (with either approach) you get N virtual Data Sets for N reference inputs. I don't thin ...

Ray,
I agree - creating many connections from transformer to Lookup stage is nothing but creating N virtual Data Sets.
But, in the transformer he can have the lookup key column mapped into few new columns which can be used in the lookup stage to map with different columns in the source (I believe this not like creating N virtual Data Sets)
Correct me if I am wrong. Thanks in advance

You're right but you can achieve the same result with much less processing using Copy stage to filter the required columns.