Talend filter from two input files

Question

I have two data files (delimited files) : - The first one contain 3 columns, ID, num_phone, trafic_etl : the sim card may be 3g, 4g or whatever. - the second one contain 1 column num_phone_4g : the sim card has to be 4g.

---

The thing is, I want to fill a oracle table, with numbers with 4g sim card (second file), that has 0 trafic_etl in total, knowing that the first file may have more than one row for same num_phone.

I did do this with sql statement by storing files in tables. But what I have to do, is using talend for and I am new to this tool.

---

Thanks in advance.

Images of the two files : File2 File1

Answer 1

Here's a solution using this sample data.

*File 1*
num_phone;trafic_etl;annee;mois;jour
123456;111111;2018;Juillet;20
123457;222222;2018;Juillet;20
123458;0;2018;Juillet;20
123456;333333;2018;Juillet;20
123457;444444;2018;Juillet;20
123458;0;2018;Juillet;20

*File 2*
num_phone_4g
123456
123457
123458
123459

The expected output is 123458 (because it has a total of 0 trafic) and 123459 (because it's not present in file 1; I don't know if this is possible in your use case).

I aggregate the data of file2 by phone number to get the total trafic for each phone number (assuming the date is not important). Then I use this aggregated data as a lookup to file2. In tMap_1, there is a join between the 2 flows on the phone number, and I only output the rows from file2 where the total trafic is null or zero.

Let me know if my assumptions are correct. If they are not, I will update my answer.