topic Re: Lookup OR condition in tMap in Talend Studio

Lookup OR condition in tMap

Anonymous — Sat, 16 Nov 2024 13:13:09 GMT

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

Re: Lookup OR condition in tMap

alevy — Sun, 07 Nov 2010 22:18:35 GMT

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.

Re: Lookup OR condition in tMap

Anonymous — Mon, 08 Nov 2010 16:35:42 GMT

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

Re: Lookup OR condition in tMap

alevy — Mon, 08 Nov 2010 22:07:15 GMT

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

Re: Lookup OR condition in tMap

Anonymous — Tue, 09 Nov 2010 04:26:15 GMT

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

Re: Lookup OR condition in tMap

alevy — Tue, 09 Nov 2010 05:10:05 GMT

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

Re: Lookup OR condition in tMap

alevy — Tue, 09 Nov 2010 05:20:31 GMT

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).

Re: Lookup OR condition in tMap

Anonymous — Sun, 14 Nov 2010 19:08:58 GMT

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.

Re: Lookup OR condition in tMap

Anonymous — Sun, 14 Nov 2010 19:15:03 GMT

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

Re: Lookup OR condition in tMap

Anonymous — Sun, 14 Nov 2010 19:26:00 GMT

repload

Re: Lookup OR condition in tMap

Anonymous — Sun, 14 Nov 2010 19:27:00 GMT

repload

Re: Lookup OR condition in tMap

alevy — Sun, 14 Nov 2010 22:05:19 GMT

I don't quite understand what you want now? The tMap example I posted should give you the records from a that don't match any field in b in one step...
Ignore my previous comment about tJavaRow; it was incorrect.
A couple of other comments about your image:
- The tReplicate multiplies your flow so it can go to more than one component. It is pointless as you've used it.
- You do not need to sort your flow to use tUniqueRow.

Re: Lookup OR condition in tMap

Anonymous — Mon, 15 Nov 2010 16:06:04 GMT

uploaded

Re: Lookup OR condition in tMap

Anonymous — Mon, 15 Nov 2010 16:07:50 GMT

uploaded

Re: Lookup OR condition in tMap

Anonymous — Mon, 15 Nov 2010 16:29:37 GMT

upload

Re: Lookup OR condition in tMap

Anonymous — Mon, 15 Nov 2010 16:32:03 GMT

try to upload

Re: Lookup OR condition in tMap

Anonymous — Mon, 15 Nov 2010 16:41:15 GMT

repload

Re: Lookup OR condition in tMap

Anonymous — Mon, 15 Nov 2010 16:47:44 GMT

upload

Re: Lookup OR condition in tMap

Anonymous — Mon, 15 Nov 2010 16:50:29 GMT

upload

Re: Lookup OR condition in tMap

Anonymous — Mon, 15 Nov 2010 17:02:45 GMT

Alevy, Thanks for your information.
Sorry to try so many times to upload, there is some problem with my screen resolution.
Please see the above two screen, originally I did the same set up as you
The expression is as the following.
row1.a1==null || !(row1.a1.equals(row2.b1) || row1.a1.equals(row2.b2) || row1.a1.equals(row2.b3))
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 as followings
1;11;66;22;33
1;11;77;;33
2;22;11;88;99
2;22;77;;33
3;33;11;88;99
4;44;11;88;99
4;44;66;22;33
4;44;77;;33
5;55;11;88;99
5;55;66;22;33
5;55;77;;33
11, 22, 33 should not be in the output, becasue these value can be found in one of the 3 fields b1, b2, b3 in the b file.
2) Another question regarding tReplicate component in the original screen I sent to you, I tried to drag two rows from tUnique and tReplicate into tMap2, but somehow, the system doesn't allow. If I first drag the row tUnique output into tMap2, then I can't add the output from tReplicate. The same problem if I drag the ouput of tReplicate first.
That is why I have to use another delimiter input file ponting to the same a file. Do you know why is that?
Hopefully this time I explain my situation clearly and you can help me. Thanks.