I have two input files and I'm trying to apply an inner join.
So I have my main input file then a lookup file.
I want to compare my main file with the lookup and then produce two outputs - one with all the values that don't appear in the lookup, and one with all the values that do appear in the lookup.
The issue is that both input files have mixed case - some upper, some lower, some proper.
I'm trying to compare them using IgnoreCase but it doesn't seem to be working.
I've specified my expr.key and then in the filter I've compared the two fields using IgnoreCase (see screenshot).
However in my main input I have the value BARCLAYS BANK PLC & in the lookup file it's Barclays Bank Plc. It's not recognising this as being the same value.
I actually managed to half resolve this but it's created another issue. For for those values which are in the lookup I can get the correct output for those by putting the expression on the output instead of the input. However, for the output that should contain the values not in the lookup, the output produces over 200k rows when it should be <500. Every row is duplicated over 400 times. I thought I would just have to reverse the expression to a not equals instead of equals and it should work, but it doesn't. I've tried the following in the output expression:
On catch output reject & lookup inner join reject, I've tried both true and false which either gives the result of >200k rows in the output or zero rows.
Screenshot attached. I would really, really appreciate if anyone could help!
Hi thanks for the response. I need to be able to retain the case of the input data. Or do you mean just transform to the same case in order to compare? How would I do that? I'm very new to talend and currently pulling my hair out over this.. 😞
Please transform the both source data to same case, either upper case or lower case using tmap components separately with inbuilt routines StringHandling.DOWNCASE() or StringHandling.UPCASE(). Then perform the join condition.
Please transform the both source data to same case, either upper case or lower case using tmap components separately with inbuilt routines StringHandling.DOWNCASE() or StringHandling.UPCASE(). Then perform the join condition.
