topic [resolved] tMap + rejecting filtered rows in Talend Studio

[resolved] tMap + rejecting filtered rows

Anonymous — Sat, 16 Nov 2024 13:11:50 GMT

Hello,
I can't figure out how to reject row from input "ok" depending on the following rule :
If ok.CPTFISAG doesn't begin with "4" then
Reject each rows of input "ok" where there's no row for ok.CBUFISAG = lm_tSACBU1.BUASACBU
Reject each rows of input "ok" where there's no row for ok.SVCFISAG = lm_tSASVC1.SVASASVC
Reject each rows of input "ok" where there's no row for ok.CPTFISAG = lm_tSADES1.CPTSADES
If I check inner join on lm_tSACBU1, lm_tSASVC1, lm_tSADES1, and Filter + Inner join Reject on the 3 output reject I got a NullPointer
Exception 😞
This happens when ok.CBUFISAG has no lm_tSACBU1.BUASACBU or ok.SVCFISAG has no lm_tSASVC1.SVASASVC or ok.CPTFISAG has no lm_tSADES1.CPTSADES
See screnshots.
Thanks for helping,
Didier

Re: [resolved] tMap + rejecting filtered rows

Anonymous — Fri, 26 Nov 2010 04:18:49 GMT

I would still use the inner join and its reject, however, would give a default value to the fields in a tJavaRow component before the tMap.

Re: [resolved] tMap + rejecting filtered rows

Anonymous — Fri, 26 Nov 2010 06:52:07 GMT

I would still use the inner join and its reject, however, would give a default value to the fields in a tJavaRow component before the tMap.

Hello,
that means you think the Null Exception comes from the main flow "ok" (that contains CPTFISAG) ?

Re: [resolved] tMap + rejecting filtered rows

Anonymous — Fri, 26 Nov 2010 14:32:30 GMT

Any idea ?

Re: [resolved] tMap + rejecting filtered rows

Anonymous — Fri, 26 Nov 2010 18:13:49 GMT

hi,
try and change into your filter "equals(null)" by "== null"
hope it help
regards
laurent

Re: [resolved] tMap + rejecting filtered rows

Anonymous — Tue, 30 Nov 2010 09:28:43 GMT

hi,
try and change into your filter "equals(null)" by "== null"
hope it help
regards
laurent

Hello, since the filtered data is type String I don't think it will work.
I have simplified my job and posted new screenshots.
Var.cgr =
(!Frais.CGRFIFRS.equals("") && !Frais.POSFIFRS.equals(""))?Frais.CGRFIFRS.substring(0,5) + NODES.DESNODES + Frais.CGRFIFRS.substring(8,Frais.CGRFIFRS.length()):""
Var.tva =
Frais.TVAFIFRS.equals("")?"":CATVA.TV2CATVA
Oups sorry I have just understood this is not the same job as the one in my first post. But the problem is the same so one will solve both 🙂
Hope this helps 🙂

Re: [resolved] tMap + rejecting filtered rows

alevy — Wed, 01 Dec 2010 00:29:12 GMT

You cannot use .equals on a variable that is null, which is what the Strings from your lookup tables will be if the join fails. You must use e.g. NODES.DE1NODES==null for your output filter.
Note that if the NODES inner-join fails, Talend will not attempt the CATVA join i.e. all the values from CATVA will be null if EITHER join fails.
To filter rejet_TVA to only include rows from Frais where the NODES join succeeds but the CATVA join fails, use NODES.DE1NODES!=null as your output filter.
If you want Talend to always attempt both joins, they must be left-joins, in which case you obviously need to have an output filter on Qualiac.

Re: [resolved] tMap + rejecting filtered rows

Anonymous — Wed, 01 Dec 2010 07:26:03 GMT

Hello alevy,
Thanks a lot !!!! You're my hero 😉
Thanks to you, now I understand joins in Talend 🙂
Best regards,
Didier.

Re: [resolved] tMap + rejecting filtered rows

Anonymous — Wed, 01 Dec 2010 12:37:54 GMT

In fact I still have a problem :
column Frais.TVAFIFRS can be null and have a corresponding record in CATVA table.
the problem is that if I test "Frais.TVAFIFRS==null?null:Frais.TVAFIFRS" for the left join with CATVA.TV1CATVA, rows containing an empty value in TVAFIFRS are rejected
but if I set the test to "Frais.TVAFIFRS.equals("")?null:Frais.TVAFIFRS" - as in the screenshot - rows containing an empty value in TVAFIFRS are NOT rejected
Why ?

Re: [resolved] tMap + rejecting filtered rows

alevy — Wed, 01 Dec 2010 22:07:37 GMT

Unlike SQL, Talend treats a join between two null fields as successful. There is no need to do "Frais.TVAFIFRS==null?null:Frais.TVAFIFRS" for the join. Just use "Frais.TVAFIFRS".
However, an empty/zero-length String ("") is not the same thing as a null (in SQL or Talend). So you need to be sure about exactly what data Frais.TVAFIFRS and CATVA.TV1CATVA contain before trying to join them. If CATVA.TV1CATVA can contain both nulls and empty strings and you want them to be treated as the same thing for your join, you need to either change one to the other for CATVA.TV1CATVA before tMap or use a lookup condition rather than a direct join to cater for both possibilities.
What you've done assumes that CATVA.TV1CATVA cannot contain empty strings (or at least that those should not result in a successful join) but Frais.TVAFIFRS can contain both and both should be matched to the null in CATVA.TV1CATVA.
Note that whether your data contains empty-strings or nulls depends on the input component you use. tInput will reflect the actual data in the database table but tFileInput will treat all Strings as empty not null.

Re: [resolved] tMap + rejecting filtered rows

Anonymous — Thu, 02 Dec 2010 07:02:58 GMT

Hello alevy,
in fact Frais is a file and CATVA is a mySQL table that can contain null. So "Frais.TVAFIFRS.equals("")?null:Frais.TVAFIFRS" seems to be the good way to join the empty value of Frais.TVAFIFRS and the null value of CATVA.TV1CATVA :-)
Thanks again for your help !!!