Linking tables by link table

[Anonymous]

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.

[sasikanth]

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;