Flattening the file with codes

[pradkumar]

Hi

I am having a source input file as
Input
Name Region
Ravi All
Ram Europe


I am having a table which is having structure and data as below:
C1 C2 C3
A Asia 10
B Asia 20
C Europe 30


Now I want to map the input file to this table and get all the codes for the corresponding region and expecting output to be as
Output

Name Region Code
Ravi All 10
Ravi All 20
Ravi All 30
Ram Europe 30

As Ravi is assigned All in the osurce file, he should get all the codes.I tried using lookup, but lookup only gets only one value
Please share any ideas on this

Thanks
Pradeep

[satya99]

Pradeep,

How does the design look like.
What are the keys.
Is source a hashed file.
Based on which key, you are performing join.

Thanks

[chulett]

If you want the lookup to return multiple records, you'll need to use either a UV stage or an ODBC stage for the looking upping.

[ray.wurlod]

You will need special handling for "All" - probably in a separate stream in the job that you can bring back together with the main stream in a Link Collector stage or by writing to an intermediate hashed file.