1 Reply Latest reply: Jun 10, 2016 7:45 AM by sasi k RSS

    Linking tables by link table

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

      Colleagues, good day!

      I have some problem with linking two tables, which have several same fields.

       

      First, i load two directories (Calendar and Nomenclature)

      Second, i load table Sales_1

      Third - make LinkTable to connect Sales_1 with LinkTable by combine autonumberhash field

      Fourth - load table Sales_2

       

      My tables Sales1 and Sales2 must be connected by fields, which is situated in Connect_Sales1_Sales2 sheet of source files.

       

      When i connect these two tables by link table, result show me some missing values.

       

       

      My necessary result:

         

      IdWareHouseDeliveryPoint_Sales2sum(Volume)sum(Volume_Sales2)
      1W156635901
      1W1576355 491
      1W2585 7576 079

      Main idea - linking Sales_1 and Linjk Table must be by key field   AutoNumberHash128(WareHouse, DeliveryPoint, Date, Nomenclature)

       

      Please, if someone have any idea how correctly make linking, please, help))

       

      Thank.

        • Re: Linking tables by link table
          sasi k

          hi,

          Try with below script

           

          LOAD Date,

              [Other date field]

          FROM

          Linking.xlsx

          (ooxml, embedded labels, table is Calendar);

           

           

          Nomenclature:

          LOAD Nomenclature,

              Nomenclature_Name

          FROM

          Linking.xlsx

          (ooxml, embedded labels, table is Nomenclature);

           

          sales2:

          LOAD  '1' as Id,

              DeliveryPoint_Sales2 as Delvery_Point ,

              Date,

              Nomenclature,

              Volume_Sales2

            

          FROM

          Linking.xlsx

          (ooxml, embedded labels, table is Sales2);

          Left Join

          LOAD DeliveryPoint_Sales2 as Delvery_Point,

              WareHouse

          FROM

          Linking.xlsx

          (ooxml, embedded labels, table is Connect_Sales1_Sales2);

           

           

          Sales1:

          LOAD Id,

              WareHouse ,

              DeliveryPoint,

              Date,

              Nomenclature,

              Volume

            

          FROM

          Linking.xlsx

          (ooxml, embedded labels, table is Sales1);

          Left Join

          LOAD DeliveryPoint_Sales2 as  Delvery_Point,

              WareHouse

          FROM

          Linking.xlsx

          (ooxml, embedded labels, table is Connect_Sales1_Sales2);

           

           

          S2:

          load *, 'Sales2' as Sale_Flag Resident sales2;

           

          Concatenate

           

          Load *,  'Sales1' as Sale_Flag Resident Sales1;

           

          Drop Table Sales1;

          Drop Table sales2;