Joiner Transformation in Informatica PowerCenter - Trouble in joining duplicate values from source with target

196 views Asked by At

I have following scenario

Source 1:    Category_DTL

Category_ID    Date         Value

1       20180101    10
1       20180110    12
2       20180101    14
3       20180101    15
4       20180101    10


Source 2:    Category_Master

Category_ID    Name Comments
1       abc C1
2       xyz C2
3       qwe C3
4       rty C4
5       qqq C5
6       www C6


I need output as:

Category_ID    Date         Value   Name    Comments
1       20180101    10  abc C1
1       20180110    12  abc C1
2       20180101    14  xyz C2
3       20180101    15  qwe C3
4       20180101    10  rty C4



But When I use joiner transformation in Informatica PowerCenter, I get following output:
Category_ID    Date         Value   Name    Comments
1       20180101    10  abc C1
1       20180110    12  
2       20180101    14  xyz C2
3       20180101    15  qwe C3
4       20180101    10  rty C4

It only matches first record from source with master table in case source have duplicate values on key column used in joining condition.

Join conditon > Category_Master.Category_ID = Category_DTL.Category_ID
Join type used > Detail outer Join
Master Table > Category_Master
Detail Table > Category_DTL

Any suggestion to get desired result is appreciated.

0

There are 0 answers