5 Replies Latest reply: Jan 11, 2018 1:19 AM by Yusuf Rawat RSS

    LINK table with mixed keys

    Yusuf Rawat

      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
          Tim Driller

          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 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;

                • Re: LINK table with mixed keys
                  Yusuf Rawat

                  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;