how to update my table when the data is no longer present

raoh — Sat, 16 Nov 2024 01:57:52 GMT

Hello community,
I'm stuck on a data update problem when it's no longer present in my reference file

The table which contains my referential :

table name : referential with some data
id | code | domain | active | period |
1 | 46L | AAAA | true | 04-2020 |
2 | 46L | BBBB | true | 04-2020 |
3 | 12M | AAAA | true | 04-2020 ]

at the beginning of every month I am provided with a new referential file (csv format) : name+period.csv => referential_05_2020.csv:
id | code | domain |
1 | 46L | AAAA |
2 | 46L | CCCC |
3 | 12M | AAAA |
3 | 12M | BBBB |

I have to update my referential table with these data.

as you can see in my file the data including code '46-L' and domain 'BBBB' is no longer present. Instead of deleting the record i want to update column "active" with false value and update period and insert new record

Here's what I'm looking for :

exepected output :

id | code | domain | active | period |
----|-------|----------|--------|-----------|
1 | 46L | AAAA | true | 04-2020 |
2 | 46L | BBBB | false | 05-2020 |
3 | 12M | AAAA | true | 04-2020 ]
4 | 46L | CCCC | true | 05-2020 |
5 | 12M | BBBB | true | 04-2020 ]

i've tried :

referential_table ------(MAIN) ----------------- [ tMap ] ------------(output) ---------- referential_table (insert or update)

(Lookup)

myCsvFile.csv

Join in my tMap :

referential_table.code = myCsvFile.code

referential_table.domain = myCsvFile.domain

match model : Unique Match

Join Model : left outer join

all I get as a result is :

id | code | domain | active | period |
1 | 46L | AAAA | true | 04-2020 |
2 | 46L | BBBB | true | 04-2020 |

I'm not getting the new line from the csv file.

help, please.

topic how to update my table when the data is no longer present in Talend Studio

how to update my table when the data is no longer present