Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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]);
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]);