Lookup OR condition in tMap

[Anonymous]

I have two inputs a and b
I need to do a lookup as the following OR condition
a.a1 = b.b1 or a.a1=b.b2 or a.a1=b.b3, if no match in all these 3 fields, go to reject file
Can I simply use one TMAP with one lookup to specify this condition to get the matched and reject?
Much appreiated

[alevy]

Yes but see this bug.
You will either need a field in your lookup that can be set as your join to access the inner join rejects (if you have to, add a dummy field with a fixed value using tJavaRow so that value can be specified as your join in tMap)
or you will need an expression filter for your matched output flow and output rejects for the remainder.

[Anonymous]

Alevy,
Thanks so much for your information. I am new to Talend. Could you explain more about the second option? My request is to create an account list where a.a1 != b.b1 and a.a1!=b.b2 and a.a1!=b.b3.
Peter

[alevy]

What data type are your fields: the correct syntax depends on the type and whether the fields are nullable?

[Anonymous]

all the 4 fileds are string(8)
only b.b1 not nullable
a.a1, b.b2, b.b3 nullable

[alevy]

See the attached screen-print. Note that with this data structure Talend will output the "a" row for each "b" row that does not match i.e. multiplying it. This can't be avoided so to have only one instance of each "a" row that does not match any "b" row, you will need to follow the tMap with a tUniqueRow. That requires that each "a" row can be uniquely identified.
Alternatively, you would have to have three left-join lookups to "b" with your output condition being: b1.b1==null && b2.b1==null && b3.b1==null

[alevy]

I should add that .equals is case-sensitive. Use .equalsIgnoreCase if you want case-insensitive matching.
Also, unless trailing spaces are important (in which case you need to be careful about the length of your strings), you should trim the incoming data (advanced option in tDBInput).

[Anonymous]

Thanks so much for your inforation. Alevy. I tried, it is working. Please see the image I uploaded.
Just a little modification.
I have 'a' file
1,11
2,22
3,33
4,44
5,55
b file
11 ,88 ,99
66 , 22,33
77 , ,33
the output is
4;44;0;;;;
5;55;0;;;;
This is what I want. for the value 44 and 55, they can't be found in all the 3 fields in the b file.
In order to do this, I need to use your expression, but just get the records which be found in any of the 3 fileds in the b file,
that is 11, 22, 33, later I use another tMap componet to get the 'a' records excluding 11,22,33.
If using your way, for 11 value, it doesn't exist in some record, but it should not go the the output. Becaue it doesn't exist in another record.
1) I think this is the best way. If you can better way, such as do it in only one tMap component, please let me know.......
2) I am wondering the first way you mentioned, how do use tJavaRow to do the same thing?
Forgive me asking so many questions. I am just a beginner.

[Anonymous]

the img is too big, changed the format and reupload it as below

[Anonymous]

repload