topic Re: getting null values as output while comparing 2 tables in Talend Studio

getting null values as output while comparing 2 tables

Anonymous — Sat, 16 Nov 2024 12:57:45 GMT

Hello, I am trying to get the unmatched records from 2 tables. I have 2 tables that are very similar with the exception of a couple of records. I use tMap to join on every field between the 2 tables. The output count from tMap matches the number of unmatched records since I am outputting the rejects however I am getting null values when I output these unmatched records to a tLogRow or to mysql or even a flat file. How can I view what this data is? Note: Unmatched record count matches 100% as I have limited the tables to 10 records for troubleshooting purposes.

Re: getting null values as output while comparing 2 tables

Anonymous — Fri, 22 Apr 2011 16:46:51 GMT

Hi
Do an inner join on tMap and get the unmatched records, see topics:
https://community.talend.com/t5/Design-and-Development/How-to-add-logs-for-each-events-in-my-job/td-p/66417
https://community.talend.com/t5/Design-and-Development/Query-from-different-databases/td-p/1234
Let me know if you have any questions!
Best regards
Shong

Re: getting null values as output while comparing 2 tables

Anonymous — Fri, 22 Apr 2011 16:54:07 GMT

Hello Shong, the Inner Join has already been done and this is how I was able to get the unmatched record count however I am still getting null when I output the reject data.

Re: getting null values as output while comparing 2 tables

Anonymous — Fri, 22 Apr 2011 16:57:03 GMT

Hi
The reject data? You meant the unmatched data, right? In the topics I listed as above, I have showed a demo job. If you still get null value, please upload some screenshots of job.
Best regards
Shong

Re: getting null values as output while comparing 2 tables

Anonymous — Fri, 22 Apr 2011 16:57:04 GMT

The Inner Join was checked with All Records and the purple reject check was clicked on to only pass in the rejects in output but I am just not getting the real values only nulls.

Re: getting null values as output while comparing 2 tables

Anonymous — Fri, 22 Apr 2011 17:19:22 GMT

How do you post screenshots here? I do not see a way to upload the image.

Re: getting null values as output while comparing 2 tables

Anonymous — Fri, 22 Apr 2011 18:21:22 GMT

hit "Post reply" and at the bottom you have a dropdown to select the number of upload slots. set this and upload each screen

Re: getting null values as output while comparing 2 tables

Anonymous — Fri, 22 Apr 2011 18:30:16 GMT

Here are the tMap component and the job screenshots

Re: getting null values as output while comparing 2 tables

Anonymous — Sat, 23 Apr 2011 07:20:19 GMT

Hello, I found yesterday a similar issue and (probably) found a solution.
I initially used the same technique explained by Shong (which is btw the same described shortly in the User Guide).
It works, but I had more than a lookup table and I wanted to add a flag telling me which (one or more) lookup failed the inner join causing the reject.
I did not manage to calculate it using the inner join, so this is how I did it :
I have row1 being the main flow, row2 and row3 are lookups where the lookup key is CustomerID (String) for row2 and EmployeeID for row3
1) Both two lookups are set to LEFT OUTER JOIN
2) I calculate two boolean variables customer_reject and employee_reject
3) I add an output flow called reject, but it is a STANDARD output, I just add the filter (Var.customer_reject || Var.employe_reject), it cointains the two flags in output
4) Obviously in the main output flow the filter is !(Var.customer_reject || Var.employe_reject)
Now, the issue I had was in calculating the flags as variables :
1) The first one is a String and when no match is found it's value is null (which is quite "normal" in left outer joins)
2) The other one was a integer and while I was expecting nulls as well, I found zeros (!!!!) 0. for this one I used a different calculation (see screenshot)
@Shong, is there a way to generate nulls instead for case 2)?
Zero can be a valid lookup code, therefore it might be a bit confusing, is it coming from some default? I looked around but found none.
Also, if I use inner joins with multiple lookups, will TOS try to match ALL the lookups if the record is unmatched with the first one?
Maybe, as a feature would be cool to have an additional variable or field automatically calculated for each lookup in inner join which gives you the rejection flag.
Thanks,
Francesco

Re: getting null values as output while comparing 2 tables

alevy — Wed, 27 Apr 2011 01:52:27 GMT

@jgomes:
-- Are you trying to output values from the main flow or the lookup flow to your inner-join rejects output? The latter values will (obviously) be null since the join failed.
-- Make sure your screenshots fit the limits for the forum i.e. smaller than 1024x768 pixels and 200 KB.
@saburo:
-- You can also use the "output rejects" flag instead of complementary filters i.e. set the filter to Var.customer_reject || Var.employe_reject on one output and just click the orange arrow on the other.
-- If EmployeeID is an int (i.e. non-nullable) it will be set to 0 if the join fails. If you want it to be null, you must change the data type to a nullable Integer. Also see http://www.talendforge.org/bugs/view.php?id=13779.
-- If you use an inner-join and it fails, Talend will not attempt ANY joins below the failed one even if the later joins are left-joins and do not depend on values from the failed join.
-- Reject flag already requested in http://www.talendforge.org/bugs/view.php?id=1723&nbn=1.