Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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;
Remove them which are Underlined then it won't produce Synthetic from sample data
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 .