Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vvvvvvizard
Contributor III

LINK table with mixed keys

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

5 Replies
zhadrakas
Valued Contributor

Re: LINK table with mixed keys

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

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;

vvvvvvizard
Contributor III

Re: LINK table with mixed keys

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

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

vvvvvvizard
Contributor III

Re: LINK table with mixed keys

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 .

Community Browser