Multiple lookups against same dataset

speedsterrules · Post by **speedsterrules** » Mon Feb 22, 2010 11:25 am

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.

Sreenivasulu · Post by **Sreenivasulu** » Mon Feb 22, 2010 11:48 am

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

Regards
Sreeni

speedsterrules · Post by **speedsterrules** » Mon Feb 22, 2010 11:52 am

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.

Sreenivasulu · Post by **Sreenivasulu** » Mon Feb 22, 2010 12:31 pm

As far as i know there are no other options

Regards
Sreeni

chulett · Post by **chulett** » Mon Feb 22, 2010 12:52 pm

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

abhijain · Post by **abhijain** » Tue Feb 23, 2010 6:02 am

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.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 23, 2010 4:07 pm

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.

dsedi · Post by **dsedi** » Wed Feb 24, 2010 4:28 pm

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.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 24, 2010 6:29 pm

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.

dsedi · Post by **dsedi** » Thu Feb 25, 2010 2:58 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Feb 25, 2010 4:05 pm

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