[resolved] tMap inner join rejects

[Anonymous]

Dear all,
I am trying to understand how to capture any records from my input file (row4) which could not be matched/joined successfully.
Despite reading the various topics I still don't understand ... perhaps I am a little slow but please help
To further clarify:
Input record is say:
ID | Number | Text
1234|22222|sometext
1235|22221|sometext
1234 was successfully inserted into the database so fine
1235 however was not matched via the inner join and is to be written away into a new delimited file hence must be caught.
I am asking how to do this; I tried setting "catch lookup inner join rejects" however that's certainly not resulting in what I need as all records of the tables are logged and not the ones from the file.

[Anonymous]

you are on the right track.
It sounds like what you need is two output tables-- one for data that joins, and one (with catch inner join rejects set) that shows you records from your main flow (row4) that would not join to your lookup (row5)

[Anonymous]

Hi John,
Thanks but I am still not getting there.... (I warned I may be a bit slow)...
Please advise on what's going so horribly wrong here... the file only got 5 rows as input and 3 match; 2 won't so basically only 2 rows should go to the rejected list ... not all those found in the database tables.
Really appreciate your (& others') help
- CPA

[alevy]

Why is there no join between row4 and row5? It would help to know what's in the lookups and what results you are expecting and getting.

[Anonymous]

Hi Alevy,
Basically the row4.AccountID is joined onto row6.REF_NO; than the matching row6.ACCOUNTS1 is joined onto row5.ACCOUNTID; well that's what I would want it to be:
I.e.
INNER JOIN ROW6 ON ROW6.REF_NO = ROW4.ACCOUNTID
INNER JOIN ROW5 ON ROW5.ACCOUNTID = ROW6.ACCOUNTS1
Does that help?

[alevy · Accepted solution]

Well you haven't done the second join in your screenshot so I'm not surprised you're not getting your expected results. Move row6 above row5 and make the join.

[Anonymous]

Awesome that was the missing piece of knowledge ... thanks so much for this