Incremental load but just for some of columns

[jasmina_karcic]

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

[JustinDallas]

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)

[jasmina_karcic]

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....