Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys I need your help about incremental load.
DATE ID MONEY ATRIBUT1 ATRIBUT2
1.8.2020. 141306 70 306 1507
Load is going every day from beginning.
Let's start
Someone changed for atribut1 on 2.8., instead 306, put 309
Standard load:
load
date,
id,
money,
atribut1,
atribut2
from table1.qvd: 1;
store table into table1.qvd
DATE ID MONEY ATRIBUT1 ATRIBUT2
1.8.2020. 141306 70 309 1507
2.8.2020. 141306 100 309 1507
We see atribut 1 for 1.8. changed also.
To avoid this situation I made incremental load.
table:
load
date,
id,
money,
atribut1,
atribut2
from table1.qvd: 1 where date<today()-1;
concatenate(table)
load
date,
id,
money,
atribut1,
atribut2
from table1.qvd: 1 where date=today()-1;
store table into table1.qvd
DATE ID MONEY ATRIBUT1 ATRIBUT2
1.8.2020. 141306 70 306 1507
2.8.2020. 141306 100 309 1507
Atribut1 is not changed and it is okay.
Now I need situation. If someone on database changed atribut1, and I want it doesn’t affect older data, I will use this incremental, but what if someone change money on 1.8. How to put new calculation?
DATE ID MONEY ATRIBUT1 ATRIBUT2
1.8.2020. 141306 80 306 1507
2.8.2020. 141306 100 309 1507
How to have 80 instead of 70 at 1.8., if I use incremental load?
thanks
This is a tad different from a run-of-the mill road. You might want to make some dummy data that someone can easily work with and create a solution.
In any case, you will one thing
1. A key to show that a row is unique/same
And you will have to use that key with a
..WHERE NOT EXISTS(KeyValue1 & '|' & KeyValue2, ExistingKeyValues)
Can you show me that on example?
My thinking was, to make 2 different load:
1. Incremental load, to keep old data for atribute1 and atribute2. Table1
2. Standard load, because money can be changed for old data. Table2
Then make left join these two tables.
Table 3:
Load
Date, id, atribute1, atribute 2 from table1.
Left join(table3)
Load
Date, id, money from table 2.
But this is too much job for load, because there are like 600 mill rows....