1 Reply Latest reply: Sep 21, 2016 7:19 AM by Krisjanis Berzs RSS

    Incremental load with update

    Krisjanis Berzs

      Hi,

      I have an excel with old data sheet (OLD):

       

      OrderId-RevenueOrderIdRevenueOther
      1-101101
      1-101102
      1-10011003
      2-5252
      2-102102
      2-152153
      2-202204
      3-503501
      4-504501
      5-505501

       

      And new excel with new data sheet (NEW)

       

      OrderId-RevenueOrderIdRevenueOther
      1-1000110002
      2-5251
      2-5253
      6-606601

       

       

      1st I would like to get result:

       

      All new data and something from old table that matches with order-revenue:

       

      OrderId-RevenueOrderIdRevenueOther
      1-1000110002
      2-5251
      2-5253
      6-606601
      2-5252

      Last row is from old table, because 2-5 matches with new table.

       

      Next, what i want is to do is do calculation, summarize 2-5 rows and the update old table with new:

       

      OrderId-RevenueOrderIdRevenueOther
      1-1000110002
      6-606601
      2-52156

       

      Full table:

       

      Old sheet + new calculated sheet

      OrderId-RevenueOrderIdRevenueOther
      1-101101
      1-101102
      1-10011003
      2-102102
      2-152153
      2-202204
      3-503501
      4-504501
      5-505501
      1-1000110002
      6-606601
      2-52156

       

      Can you  help me please?

        • Re: Incremental load with update
          Krisjanis Berzs

          I figured out myself:

           

          table1:

          LOAD

          [OrderId-Revenue],

          OrderId,

               Revenue,

               Other

          FROM

          [C:\Users\kjb\Desktop\QV_load.xlsx]

          (ooxml, embedded labels, table is New);

          //where not exists([OrderId-Revenue]);

           

           

          LOAD

          [OrderId-Revenue],

          OrderId,

               Revenue,

               Other

          FROM

          [C:\Users\kjb\Desktop\QV_load.xlsx]

          (ooxml, embedded labels, table is Old)

          where exists([OrderId-Revenue]);

           

           

          NoConcatenate

          table2:

          load

          [OrderId-Revenue],

          OrderId,

          sum(Revenue) as Revenue,

          sum(Other) as Other

          Resident table1

          group by [OrderId-Revenue],OrderId ;

           

           

          drop tables table1;

           

           

          LOAD

          [OrderId-Revenue],

          OrderId,

               Revenue,

               Other

          FROM

          [C:\Users\kjb\Desktop\QV_load.xlsx]

          (ooxml, embedded labels, table is Old)

          where not  exists([OrderId-Revenue]);