topic Re: Detecting rows that don't exist anymore based on columns in Talend Studio

Detecting rows that don't exist anymore based on columns

Mirmae — Sun, 10 May 2020 14:18:16 GMT

Hello Community,
I'm struggling with a problem;
I have 2 tFileInputDelimeted (one for today's movements, one for yesterday's) , the idea is to extract the rows that exist in the first file but not the second based on some criteria.
For example, I need to detect the the rows in the first file which don't exist in the second one based on 3 columns (ID, Code, Category), the ID is not unique as a person can have many actvities with the same ID.
Example of data:
File 1:
ID;Code;Category;Name;...
Row 1) 10005873350;70;C;Fabien;...
Row 2) 10005873350;70;M;Fabien;...
Row 3) 10005884968;60;M;Michael;...
File 2:
ID;Code;Category;Name;...
Row 1*) 10005873350;70;C;Fabien;...
Row 2*) 10005873350;70;M;Fabien;...
Row 3*) 10005884968;69;M;Michael;...

I only need to detect the rows in the File 2 with the same ID but where Code or Category changed, on condition that there isn't another row with the same ID,Code and Category. In the above example only row 3 should be detected.
I tried to do an inner join, joined by Id and added a filter (File1.Code!=File2.Code || File1.Category!=File2.Category) and even tried the inner join reject, my problem is that Row 1 and 2 will be extracted where they shouldn't be, as Row1 detects the change in Row 2* and Row 2 detects the change in Row 1*.

Thanks in advance.

Re: Detecting rows that don't exist anymore based on columns

ThWabi — Thu, 14 May 2020 19:28:19 GMT

Hello Mirmae,

an inner join on ID and Code and Category and collecting the inner join rejects does not work for your data?

Best regards,

Thomas

Re: Detecting rows that don't exist anymore based on columns

Mirmae — Thu, 14 May 2020 22:55:45 GMT

Hello Thomas, thank you for your reply,
Unfortunately, that doesn't do the job; an inner join on ID, Code and Category would detect even the rows where ID is different and I need to compare rows that have the same ID but the Code or Category changed. Correct me if I'm wrong but as I understood Inner Join Reject detects the rows where any of the columns fails, I want to detect the rejets where ID AND Code AND Category don't exist anymore like a combination instead of the default behaviour of inner join reject ID OR Code or Category fails.
I even tried an inner join only on ID and added a filter Code1 != Code2 || Category1 != Category2 yet it fails in the example illustrated above, when multiple rows exist in the two files.
Regards,
Bacem

Re: Detecting rows that don't exist anymore based on columns

ThWabi — Tue, 19 May 2020 07:27:13 GMT

Hello Bacem,

my first answer/question focused on the example data you provided. But your data has more varieties.

@Mirmae wrote:

added a filter Code1 != Code2 || Category1 != Category2

May I ask where you placed that filter? If it was on the output side of the tMap, I want to suggest that you put in the middle in the Var pane and have the expressions evaluated there.

(On the input side, use an inner join on ID with 'all matches' as you (probably) did before.)

Best regards,

Thomas