One Star

bug? tmap to catch multiple lookup inner join rejects

I have three data sets :
row1(main)
user_id                       paytype                isvalid
U987788                     salaried                Y
U987789                     monthly                N
U987790                     salaried                Y
U987791                     salaried                T

row2(lookup)
paytype                    text
salaried                    monthly
Variable                   Variable
row3(lookup)
status                    text
Y                          active
N                         inactive
join between row1 and row2 on paytype, and join row1 and row3 on  isvalid= status.
Lookup Model : Load Once
Match Model : All Matches 
Join Model : Inner Join 
two reject output:
paytypeReject
catch output reject: false
catch lookup inner join reject: true
schema type: built-in
filter: row2.paytype == null
statusReject
catch output reject: false
catch lookup inner join reject: true
schema type: built-in
filter: row3.status == null
then Output is : 
paytypeReject
user_id                       paytype                isvalid
U987789                     monthly                N
statusReject
user_id                       paytype                isvalid
U987789                     monthly                N                          ---> this record is not expected
U987791                     salaried                T
when I exchanged the sequence of row2 and row3 in tmap, then output will be:
paytypeReject
user_id                       paytype                isvalid
U987789                     monthly                N                        
U987791                     salaried                T                         ---> this record is not expected
statusReject
user_id                       paytype                isvalid
U987791                     salaried                T
It seems that if the data is included in first inner join reject, then the following inner join rejects will contain those data.
I want to fetch the reject data for each lookup in one tmap, can anyone please help?
Version: 5.6.1
Build id: V5.6.1_20141207_1530
2 REPLIES
Employee

Re: bug? tmap to catch multiple lookup inner join rejects

I'm not sure if this is a bug as I would need to see the job and have a play to work that out. However to get round this and get the result you want, why don't you use the "Left outer join" join model? Then you will get all of your rows back and you can filter into each of your output tables simply using the filters rather than using the "Catch...reject" options.
One Star

Re: bug? tmap to catch multiple lookup inner join rejects

Hi Rhall,
thanks for your suggestion.
I just want to simplify the job, and found this issue.