DSXchange

In my job design i have one source(hash file) and 1 lookups( hash file).

When i find a value in lookup as A1 then i need to concatenate the value coming from source with 1 and hook that value
When i find a value in lookup as A2 then i need to concatenate the value coming from source with 2 and hook that value
When i find a value in lookup as A3 then i need to concatenate the value coming from source with 3 and hook that value


source

A

LOOKUP1

A1
A2
A3
..

Any idea.

thanks in advance

Are you saying tht the lookup can have A1 , A2 and A3 at the same time? or a single value?

If it has all the values then what should be assigned to the output?

If only one is present in the lookup then here is the way u can handle it --

Create the Hashed file with two columns - One column Substring(Lookup , 1,1) which can be the key column , the second column with the actual value.
When the Value matches it will pull out the actual column.

What is the output u want from the job?

As per ur data, there there is 1 rows in source and in the hash file there is matching row.
The basic rule of using hash file as a lookup is that u need to have one key field in it which will match the source(driving table) table's field.

If u elaborate and give some more sample data then some one will be able to give appropriate solution.

DeepakCorning wrote:Are you saying tht the lookup can have A1 , A2 and A3 at the same time? or a single value?

If it has all the values then what should be assigned to the output?

If only one is present in the lookup then here is the way u can handle it --

Create the Hashed file with two columns - One column Substring(Lookup , 1,1) which can be the key column , the second column with the actual value.
When the Value matches it will pull out the actual column.

if u use Substring(Lookup , 1,1), then as per data provided it will look like
A
A
A
..
then how u can make it as key field?

rafik2k wrote:

DeepakCorning wrote:Are you saying tht the lookup can have A1 , A2 and A3 at the same time? or a single value?

If it has all the values then what should be assigned to the output?

If only one is present in the lookup then here is the way u can handle it --

Create the Hashed file with two columns - One column Substring(Lookup , 1,1) which can be the key column , the second column with the actual value.
When the Value matches it will pull out the actual column.

if u use Substring(Lookup , 1,1), then as per data provided it will look like
A
A
A
..
then how u can make it as key field?

Please read "Are you saying tht the lookup can have A1 , A2 and A3 at the same time? or a single value?

If it has all the values then what should be assigned to the output? "

Your job design needs three lookups, not one.

ray.wurlod wrote:Your job design needs three lookups, not one. ...

that means if hash file have data like following then we need 4 lookup?

LOOKUP1
A1
A2
A3
A4

Yes, if your logic is that posted by satya99.

How many maximum concatenated running numbers can you have? If you have a max then you can generate the extra rows. Do a simple lookup and you will automatically get a hit on the hashed file with the key you want.