Linking tables by link table

Anonymous — Thu, 09 Jun 2016 07:10:37 GMT

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.

Re: Linking tables by link table

sasikanth — Fri, 10 Jun 2016 11:45:24 GMT

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;

topic Linking tables by link table in QlikView

Linking tables by link table

Re: Linking tables by link table