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.