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
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.
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 🙂
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.
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 ?
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.
