duplicate records at reload (incremental update and insert)

ioannagr — Tue, 23 Mar 2021 15:20:31 GMT

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

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.

Re: duplicate records at reload (incremental update and insert)

ioannagr — Wed, 24 Mar 2021 13:06:36 GMT

Anyone please?

topic Re: duplicate records at reload (incremental update and insert) in App Development

duplicate records at reload (incremental update and insert)

Re: duplicate records at reload (incremental update and insert)