topic Re: Getting rows with differences between two data tables in Talend Studio

Getting rows with differences between two data tables

JABELLA1634831127 — Fri, 15 Nov 2024 23:23:12 GMT

Hi so I currently have this tmap which gets all rows with differences between two tables. The problem is it also gets additional rows in the first table that is not in the second table. How can I get only rows with differences not including rows present in data table 1 but not present in data table 2.

Re: Getting rows with differences between two data tables

gjeremy1617088143 — Wed, 22 Dec 2021 10:04:18 GMT

Hi maybe you can add a filter expression on the output to check if a specific field of row6 is null, to throw away row from row5 wich are not present in row6.

Send me love and kudos

Re: Getting rows with differences between two data tables

JABELLA1634831127 — Wed, 22 Dec 2021 10:14:15 GMT

Hi sorry can you give me an example, I've never tried using filter expression.

Re: Getting rows with differences between two data tables

gjeremy1617088143 — Wed, 22 Dec 2021 10:20:11 GMT

https://help.talend.com/r/en-US/7.3/tmap/filter-expressions

and inside for eg you can add :

row6.Short_Name != null

cause when you catch inner join reject you also catch record wich are present in row5 and not row 6 so these record have null value in all their fields in row 6

Re: Getting rows with differences between two data tables

JABELLA1634831127 — Wed, 22 Dec 2021 10:28:46 GMT

When I tried this one, I got 0 rows as my output.

Re: Getting rows with differences between two data tables

gjeremy1617088143 — Wed, 22 Dec 2021 10:33:09 GMT

you have to use a field wich is not nullable maybe by default your short name is always null

Re: Getting rows with differences between two data tables

JABELLA1634831127 — Wed, 22 Dec 2021 10:55:11 GMT

Does this mean unticking the nullable column?

Re: Getting rows with differences between two data tables

gjeremy1617088143 — Wed, 22 Dec 2021 13:14:45 GMT

i've testing it and the row6 column will be always null due to inner join reject, have you a specific key to identify input wich can be already exist ?

if you have then first make an inner join on the key then after you get the result you can check difference between the two table

Re: Getting rows with differences between two data tables

JABELLA1634831127 — Wed, 22 Dec 2021 13:17:06 GMT

My specific key is short_name, both tables don't have null values in that key.

Re: Getting rows with differences between two data tables

gjeremy1617088143 — Wed, 22 Dec 2021 13:40:53 GMT

so first make an inner join only on the short name , then with the output make an inner join with all the fields and get the catch inner join reject as the result. For the first join instead of a tmap you can use a tJoin component , you have to call the second table for the two join so you can stock it in a hash and call the hash two time.

Re: Getting rows with differences between two data tables

JABELLA1634831127 — Wed, 22 Dec 2021 14:47:38 GMT

Would this be correct?

Re: Getting rows with differences between two data tables

gjeremy1617088143 — Wed, 22 Dec 2021 15:23:57 GMT

no :

first send tfileinputexcel6 to a hash,

then onsubjobok link,

you read your main file you join with a hash (with tJoin) and the output of this is joined to a second hash in a tMap (both HashInput a relied to the same hashInput, on the second one you check the checkbox for deleting the hash after reading the data)

Re: Getting rows with differences between two data tables

gjeremy1617088143 — Wed, 22 Dec 2021 15:30:48 GMT

something like this :

Re: Getting rows with differences between two data tables

JABELLA1634831127 — Wed, 22 Dec 2021 16:24:49 GMT

Hi really sorry I'm asking so much but how should my tmap look like. I followed the picture above but I think my tmap is wrong, since im getting null values in all my rows.

Re: Getting rows with differences between two data tables

gjeremy1617088143 — Wed, 22 Dec 2021 16:29:38 GMT

you have to set row8 instead of row9 in the ouptut schema

Re: Getting rows with differences between two data tables

JABELLA1634831127 — Wed, 22 Dec 2021 16:36:01 GMT

thanks for all your help. it works now 🙂

Re: Getting rows with differences between two data tables

gjeremy1617088143 — Wed, 22 Dec 2021 16:38:21 GMT

don't forget to select one of my answer as the best and merry Christmas