1 Reply Latest reply: Dec 11, 2015 2:28 AM by Manish Kachhia RSS

    Join values for every value of other table

    Андрей Шепель

      Good day!

      i have test model, with one table:

       

      Main:

      LOAD [Distributor _id],

           Date,

           Volume

      FROM

      [C:\Users\09132202\Desktop\Test.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

         

      Distributor _idDateVolume
      110.12.2015233
      111.12.2015234
      112.12.2015235
      113.12.2015236
      114.12.2015237
      115.12.2015238
      116.12.2015239
      117.12.2015240
      118.12.2015241
      119.12.2015242
      120.12.2015243
      121.12.2015244
      122.12.2015245
      210.12.2015233
      211.12.2015234
      212.12.2015235
      219.12.2015242
      220.12.2015243
      221.12.2015244
      222.12.2015245

       

       

      For this table i join dates from other source:

       

      outer Join(Main)

      load

      Date

       

      FROM

      [C:\Users\09132202\Desktop\Test.xlsx]

      (ooxml, embedded labels, table is Sheet2);

       

      Result table:

       

         

      Distributor _idDateVolume
      110.12.2015233
      111.12.2015234
      112.12.2015235
      113.12.2015236
      114.12.2015237
      115.12.2015238
      116.12.2015239
      117.12.2015240
      118.12.2015241
      119.12.2015242
      120.12.2015243
      121.12.2015244
      122.12.2015245
      210.12.2015233
      211.12.2015234
      212.12.2015235
      219.12.2015242
      220.12.2015243
      221.12.2015244
      222.12.2015245
      10.11.2015
      11.11.2015
      12.11.2015

       

      But i need to connect Dates from second source for every Distributor _id.

       

      Necessary table:

       

         

      Distributor _idDateVolume
      110.11.2015
      111.11.2015
      112.11.2015
      110.12.2015233
      111.12.2015234
      112.12.2015235
      113.12.2015236
      114.12.2015237
      115.12.2015238
      116.12.2015239
      117.12.2015240
      118.12.2015241
      119.12.2015242
      120.12.2015243
      121.12.2015244
      122.12.2015245
      210.11.2015
      211.11.2015
      212.11.2015
      210.12.2015233
      211.12.2015234
      212.12.2015235
      219.12.2015242
      220.12.2015243
      221.12.2015244
      222.12.2015245

       

      Please, help!

      Thanks.

        • Re: Join values for every value of other table
          Manish Kachhia
          Main:
          LOAD [Distributor _id], 
               Date, 
               Volume
          FROM
          [Test.xlsx]
          (ooxml, embedded labels, table is Sheet1);
          
          
          Temp:
          Load Distinct [Distributor _id] Resident Main
          Where Not IsNull([Distributor _id]) or Len(Trim([Distributor _id])) <> 0;
          Join
          Load 
            Date
          FROM
          [Test.xlsx]
          (ooxml, embedded labels, table is Sheet2)
          Where Len(Trim(Date)) <> 0 or Not IsNull(Date);
          
          
          Concatenate(Main) Load * Resident Temp;
          
          
          Drop Table Temp;