Lookup to multiple rows

[shrey3a]

Hi,

I've a scenario in look up as stated below.

Source link

Crse_CD Col1 Col2 Col3 Col4 Col5
M10001 1 a c c b
M20001 4 s d d d

Look up

Crse_cd
M10001
M10001
M10001
M20002
M20002

I've to do a look up on Crse_cd column and divide my source in to no of occurence of Crse_cd in look up link i.e. M10001 comes 3 times in lookup and M20002 comes 2 times so my o/p should be 3rows(M10001)+2rows(M20002) = 5 rows of data.
Can anybody help on this .

Thanks in advance.

Regards,
Munish

[ray.wurlod]

Only ODBC and UV stage types support multi-row return on a lookup. Search the forum or check Designer help (search there for "multi").

[vmcburney]

Can you flip your job around, make the lookup your source and make the primary stream your lookup.

[ranga1970]

vmcburney had the solution for you;

Hope you are aware hash files filters the duplicates, if are using lookup through hash file only last record with tha same key column will pass through .

when you pass through hash file
Crse_cd
M10001
M10001
M10001
M20002
M20002 where Crse_cd is key column your output will be


M10001
M20002

so there is no way to get the output you desire with the links as is, only the best scenario would be

make

Crse_cd
M10001
M10001
M10001
M20002
M20002
as source link and

Crse_CD Col1 Col2 Col3 Col4 Col5
M10001 1 a c c b
M20001 4 s d d d

as a reference link

thanks

[Sainath.Srinivasan]

If both are seq files, try using merge stage. You can otherwise use ODBC and enable multiple rows to be returned.