Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Colleagues, good day!
I have some problem with linking two tables, which have several same fields.
First, i load two directories (Calendar and Nomenclature)
Second, i load table Sales_1
Third - make LinkTable to connect Sales_1 with LinkTable by combine autonumberhash field
Fourth - load table Sales_2
My tables Sales1 and Sales2 must be connected by fields, which is situated in Connect_Sales1_Sales2 sheet of source files.
When i connect these two tables by link table, result show me some missing values.
My necessary result:
Id | WareHouse | DeliveryPoint_Sales2 | sum(Volume) | sum(Volume_Sales2) |
1 | W1 | 56 | 635 | 901 |
1 | W1 | 57 | 635 | 5 491 |
1 | W2 | 58 | 5 757 | 6 079 |
Main idea - linking Sales_1 and Linjk Table must be by key field AutoNumberHash128(WareHouse, DeliveryPoint, Date, Nomenclature)
Please, if someone have any idea how correctly make linking, please, help))
Thank.
hi,
Try with below script
LOAD Date,
[Other date field]
FROM
Linking.xlsx
(ooxml, embedded labels, table is Calendar);
Nomenclature:
LOAD Nomenclature,
Nomenclature_Name
FROM
Linking.xlsx
(ooxml, embedded labels, table is Nomenclature);
sales2:
LOAD '1' as Id,
DeliveryPoint_Sales2 as Delvery_Point ,
Date,
Nomenclature,
Volume_Sales2
FROM
Linking.xlsx
(ooxml, embedded labels, table is Sales2);
Left Join
LOAD DeliveryPoint_Sales2 as Delvery_Point,
WareHouse
FROM
Linking.xlsx
(ooxml, embedded labels, table is Connect_Sales1_Sales2);
Sales1:
LOAD Id,
WareHouse ,
DeliveryPoint,
Date,
Nomenclature,
Volume
FROM
Linking.xlsx
(ooxml, embedded labels, table is Sales1);
Left Join
LOAD DeliveryPoint_Sales2 as Delvery_Point,
WareHouse
FROM
Linking.xlsx
(ooxml, embedded labels, table is Connect_Sales1_Sales2);
S2:
load *, 'Sales2' as Sale_Flag Resident sales2;
Concatenate
Load *, 'Sales1' as Sale_Flag Resident Sales1;
Drop Table Sales1;
Drop Table sales2;