2 Replies Latest reply: Dec 17, 2013 7:28 AM by Fredrik Wikberg RSS

    Fill Records for Link Table

      Hi

      I am currently creating a data model with a Link Table. In this data I want to be able to link to dimension tables with each other.

      I have created two tables one with the distinct values that I want to create records for in the Master Link Table and one table with the dates that I want to create records for.

       

      It looks something like this.

       

      Company Link Table:

      Company Number, Sales Person ID

      Company 1, Salesman 1

      Company 1, Salesman 2

      Company 2, Salesman 1

      ………

      Company 30, Salesman 15

       

      Date Range:

      Date

      2014-01-01,

      2014-02-01,

      …….

      2015-01-01

       

      Now I want to create one table that has all possible values for the distinct values of Company Link Table and Date Range.

       

      Link Table:

      Company Number, Sales Person ID, Date

      Company 1, Salesman 1, 2014-01-01

      Company 1, Salesman 2, 2014-01-01

      Company 1, Salesman 1, 2014-02-01

      Company 1, Salesman 2, 2014-02-01

      Company 2, Salesman 1, 2014-01-01

      ………..

      Company 30, Salesman 15, 2015-01-01

       

      I don’t know how to make the last step where I join the two table together and creates the records that are missing. I can’t use a join or concatenate since I don’t have values that are shared in both tables.

       

      Happy for any help.

      //Fredrik

        • Re: Fill Records for Link Table
          Srikanth P

          Just simply do the cross join between the tables. Please fin;d the sample like below:

           

          MasterLinkTable:

          LOAD * INLINE [

          Company Number, Sales Person ID

          Company 1, Salesman 1

          Company 1, Salesman 2

          Company 2, Salesman 1

          Company 2, Salesman 2

          ] ;

          Join (MasterLinkTable)

          LOAD * INLINE [

          DATE

          2014-01-01

          2014-02-01

          ];


          Your final table look like below as you required:

          Company Number, Sales Person ID, DATE

          Company 1, Salesman 1 , 2014-01-01

          Company 1, Salesman 1 , 2014-02-01

          Company 1, Salesman 2 , 2014-01-01

          Company 1, Salesman 2 , 2014-02-01

          Company 2, Salesman 1 , 2014-01-01

          Company 2, Salesman 1 , 2014-02-01

          Company 2, Salesman 2 , 2014-01-01

          Company 2, Salesman 2 , 2014-02-01