topic Re: LINK table with mixed keys in QlikView

LINK table with mixed keys

vvvvvvizard — Tue, 09 Jan 2018 06:44:29 GMT

How do you create a link table for the data model

table 1 has 2 fields in common with other 4 tables

table 2 has 3 fields in common with other 3 tables

table 3 has 4 fields in common with 2 tables

The tables hae mixed granualarities , one is static , one is month end balance , once is daily .

table 1

product id /supplier id

table 2

product id/ supplier id / customer id

table 3

product id/ supplier id / customer id/debtor id

table 4

product id/ supplier id / customer id/debtor id

Re: LINK table with mixed keys

zhadrakas — Tue, 09 Jan 2018 07:21:09 GMT

your link table can look like:

LINK TABLLE:

product id &'_'& supplier id as product id_supplier id_KEY,

product id &'_'& supplier id &'_'& customer id as product id_supplier id_customer id_KEY,

product id &'_'& supplier id &'_'& customer id &'_'& debtor id as product id_supplier id_customer id_debtor id_KEY,

product id,

supplier id,

customer id,

debtor id

think about using autonumber function to generate the keys

Autonumber function

Re: LINK table with mixed keys

Anil_Babu_Samineni — Tue, 09 Jan 2018 07:34:58 GMT

This would be give more clear

table 1

product id /supplier id

table 2

product id/ supplier id / customer id

table 3

product id/ supplier id / customer id/debtor id

table 4

product id/ supplier id / customer id/debtor id

LinkTable:

Load AutoNumber([product id] & '|' & [supplier id]) as %Key, [product id], [supplier id] Resident table1;

Concatenate(LinkTable)

Load AutoNumber([product id] & '|' & [supplier id] & '|' & [customer id]) as %Key, [product id], [supplier id], [customer id] Resident table2;

Concatenate(LinkTable)

Load AutoNumber([product id] & '|' & [supplier id] & '|' & [customer id] & '|' & [debtor id]) as %Key, [product id], [supplier id], [customer id], [debtor id] Resident table3;

Concatenate(LinkTable)

Load AutoNumber([product id] & '|' & [supplier id] & '|' & [customer id] & '|' & [debtor id]) as %Key, [product id], [supplier id], [customer id], [debtor id] Resident table4;

Drop Fields [product id], [supplier id] From table1;

Drop Fields [product id], [supplier id], [customer id] From table2;

Drop Fields [product id], [supplier id], [customer id], [debtor id] From table3;

Drop Fields [product id], [supplier id], [customer id], [debtor id] From table4;

Re: LINK table with mixed keys

vvvvvvizard — Wed, 10 Jan 2018 13:36:33 GMT

I amended your code with the underlined lines of code , that table needs that key to connect to various other tables . This datamodel now creates a synthetic key with the extra key added , is this the best way to be doing things ?

LinkTable:

Load AutoNumber([product id] & '|' & [supplier id]) as %Key, [product id], [supplier id] Resident table1;

Concatenate(LinkTable)

Load AutoNumber([product id] & '|' & [supplier id] & '|' & [customer id]) as %Key, [product id], [supplier id], [customer id] Resident table2;

Concatenate(LinkTable)

Load AutoNumber([product id] & '|' & [supplier id] & '|' & [customer id] & '|' & [debtor id]) as %Key

([product id] & '|' & [customer id] as key_2,

, [product id], [supplier id], [customer id], [debtor id] Resident table3;

Concatenate(LinkTable)

Load AutoNumber([product id] & '|' & [supplier id] & '|' & [customer id] & '|' & [debtor id]) as %Key,

([product id] & '|' & [customer id] as key_2

[product id], [supplier id], [customer id], [debtor id] Resident table4;

Drop Fields [product id], [supplier id] From table1;

Drop Fields [product id], [supplier id], [customer id] From table2;

Drop Fields [product id], [supplier id], [customer id], [debtor id] From table3;

Drop Fields [product id], [supplier id], [customer id], [debtor id] From table4;

Re: LINK table with mixed keys

Anil_Babu_Samineni — Wed, 10 Jan 2018 13:45:34 GMT

Remove them which are Underlined then it won't produce Synthetic from sample data

Re: LINK table with mixed keys

vvvvvvizard — Thu, 11 Jan 2018 06:18:49 GMT

let me create a sample model , and show you why they needed to get the correct result . Then you can show me a better way to do it .