2 Replies Latest reply: Jan 12, 2012 9:52 AM by Miguel Angel Baeyens de Arce RSS

    concatenate 2 sources

      Hi,

       

      I have 2 sheets which I want to load and concatenate. The 2 tables are in different xls docs,

      both tables have some common datafields, and one unique field : Country and Plant.

       

      Market_Stock:

      LOAD Month,

           Year,

           Country,

           [Product Group],

           [Quantity /kt],

           Date(MakeDate(Year,Month),'MM.YYYY') AS Period

      FROM

      C:\Analyses\stocks\market.xlsx

      (ooxml, embedded labels, table is db);

       

       

      Plant_Stock:

      LOAD Month,

           Year,

           Plant,

           [Product Group],

           [Quantity /kt],

           Date(MakeDate(Year,Month),'MM.YYYY') AS Period

      FROM

      C:\Analyses\stocks\plant.xlsx

      (ooxml, embedded labels, table is db);

       

       

      How do I concatenate both together, so I have the following result table:

       

      Total_Stock:

      Month, Year, Plant, Country, [Product Group], [Quantity /kt], Period

      and the empty fields get a null value

       

      Thanks a 1000 times!

        • concatenate 2 sources
          Stefan Wühl

          Try

           

          Total_Stock:

          LOAD Month,

               Year,

               Country,

               [Product Group],

               [Quantity /kt],

               Date(MakeDate(Year,Month),'MM.YYYY') AS Period

          FROM

          C:\Analyses\stocks\market.xlsx

          (ooxml, embedded labels, table is db);

           

          Concatenate LOAD Month,

               Year,

               Plant,

               [Product Group],

               [Quantity /kt],

               Date(MakeDate(Year,Month),'MM.YYYY') AS Period

          FROM

          C:\Analyses\stocks\plant.xlsx

          (ooxml, embedded labels, table is db);

          • Re: concatenate 2 sources
            Miguel Angel Baeyens de Arce

            Hi,

             

            I would join them, provided they share the same format in each field as well as the name:

             

            Total_Stock:
            LOAD Month,
                 Year,
                 [Product Group],
                 [Quantity /kt],
                 Date(MakeDate(Year,Month),'MM.YYYY') AS Period,
                 Country
            FROM
            C:\Analyses\stocks\market.xlsx
            (ooxml, embedded labels, table is db);
            
            Plant_Stock: // not needed, but useful for debugging
            LEFT JOIN (Total_Stock) LOAD Month,
                 Year,
                 [Product Group],
                 [Quantity /kt],
                 Date(MakeDate(Year,Month),'MM.YYYY') AS Period,
                 Plant
            FROM
            C:\Analyses\stocks\plant.xlsx
            (ooxml, embedded labels, table is db);
            

             

            Hope that helps.

             

            Miguel