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: 
vvvvvvizard
Partner - Specialist
Partner - Specialist

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
Specialist II
Specialist II

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

Anil_Babu_Samineni

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vvvvvvizard
Partner - Specialist
Partner - Specialist
Author

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;

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vvvvvvizard
Partner - Specialist
Partner - Specialist
Author

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 .