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.
