Incremental load with update

[krisjanis]

Hi,

I have an excel with old data sheet (OLD):

OrderId-Revenue	OrderId	Revenue	Other
1-10	1	10	1
1-10	1	10	2
1-100	1	100	3
2-5	2	5	2
2-10	2	10	2
2-15	2	15	3
2-20	2	20	4
3-50	3	50	1
4-50	4	50	1
5-50	5	50	1

And new excel with new data sheet (NEW)

OrderId-Revenue	OrderId	Revenue	Other
1-1000	1	1000	2
2-5	2	5	1
2-5	2	5	3
6-60	6	60	1

1st I would like to get result:

All new data and something from old table that matches with order-revenue:

OrderId-Revenue	OrderId	Revenue	Other
1-1000	1	1000	2
2-5	2	5	1
2-5	2	5	3
6-60	6	60	1
2-5	2	5	2

Last row is from old table, because 2-5 matches with new table.

Next, what i want is to do is do calculation, summarize 2-5 rows and the update old table with new:

OrderId-Revenue	OrderId	Revenue	Other
1-1000	1	1000	2
6-60	6	60	1
2-5	2	15	6

Full table:

Old sheet + new calculated sheet

OrderId-Revenue	OrderId	Revenue	Other
1-10	1	10	1
1-10	1	10	2
1-100	1	100	3
2-10	2	10	2
2-15	2	15	3
2-20	2	20	4
3-50	3	50	1
4-50	4	50	1
5-50	5	50	1
1-1000	1	1000	2
6-60	6	60	1
2-5	2	15	6

Can you  help me please?

[krisjanis · Accepted solution]

I figured out myself:

table1:

LOAD

[OrderId-Revenue],

OrderId,

     Revenue,

     Other

FROM

(ooxml, embedded labels, table is New);

//where not exists([OrderId-Revenue]);

LOAD

[OrderId-Revenue],

OrderId,

     Revenue,

     Other

FROM

(ooxml, embedded labels, table is Old)

where exists([OrderId-Revenue]);

NoConcatenate

table2:

load

[OrderId-Revenue],

OrderId,

sum(Revenue) as Revenue,

sum(Other) as Other

Resident table1

group by [OrderId-Revenue],OrderId ;

drop tables table1;

LOAD

[OrderId-Revenue],

OrderId,

     Revenue,

     Other

FROM

(ooxml, embedded labels, table is Old)

where not  exists([OrderId-Revenue]);