Hello everyone.
I have done a similar thread before but now i'm more spot on on what i need you to help me with.
I want to achieve Insert and update (no delete) incremental load.
I create the qvd, it's fine, the records are all there. I create the incremental load as always and at the final concatenation I add where not exists (primary key) as advised. When I reload, I see duplicate rows for each id, even if no change has happened to an id. For example let's say i did a change for id=1 from yes to no, i get
id state updated
1 yes 22/3/2021
1 no 23/3/2021
2 yes 22/3/2021
2 yes 22/3/2021
and so on.
I'm thinking the problem lies in that my initial qvd has a complex source table, but i do that to avoid joins with very large (100M) lines each time from source.
Have a look at my qvd and incremental script (so and so, as an idea)
inital qvd
Initial:
Load id, state
sql select from sourcetable1;
STORE Initial INTO '$(vPathQVD)/Initial.qvd';
incremental load
// have already precalculated last_update and saved it into a variable
Initial:
//notice there isnt a modification date here but I get it from another table that they connect with in SQL
//SELECT part
Load id, state
sql select a.id, a.state,b.updated from sourcetable1
inner join sourcetable2 b on a.id=b.id where b.updated>='$(Last_Update)'
Concatenate
Load * from [$(vPathQVD)/Initial.qvd] (qvd)
WHERE NOT Exists(id);
Please help! I really need some light on this. On my example table you see updated because as i said the tables normally exist together but to save time i amde them into qvds.
Thanks in advance.
