Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Linking tables by link table

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:

   

IdWareHouseDeliveryPoint_Sales2sum(Volume)sum(Volume_Sales2)
1W156635901
1W1576355 491
1W2585 7576 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.

1 Reply
sasikanth
Master
Master

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;