3 Replies Latest reply: Feb 16, 2016 8:30 AM by Riley MacDonald RSS

    Group By Load Script With Formula

    Riley MacDonald

      Hi All,

       

      I am having difficulties with the Group By Clause. I am taking a table with daily data which is mapped to a month and trying to aggregate it on the month level and do some basic calculations, however whenever I do this my straight table will give me duplicate rows as the Inventory Turns field will show up multiple times for the SKU (should only show up once per month since SKU is a unique indicator). Here is my script:

       

      secondTable:

      LOAD

      [Market],

      [Month],

      [SKU],

      [Model],

      [Supplier],

      Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3])  as [Total Inv],

      Sum([Sales]) as [Total Sales],

      Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3]) / Sum([Sales] as [Inventory Turns]

      Resident firstTable Group by [Market], [Month], [SKU], [Model], [Supplier];

       

      Here is the firstTable:

      MarketMonthDaySKUModelSupplierInv1Inv2Inv3Sales
      CanadaJan-1601/01/2016ABCModel 1Supplier 118191718
      CanadaJan-1602/01/2016ABCModel 1Supplier 113121717
      CanadaJan-1603/01/2016ABCModel 1Supplier 111171511
      CanadaJan-1604/01/2016ABCModel 1Supplier 119201515
      CanadaJan-1605/01/2016ABCModel 1Supplier 1132020

      20

       

      Here would be the expected result of secondTable:

         

      MarketMonthDaySKUModelSupplierTotal InvTotal SalesInventory Turns
      CanadaJan-1601/01/2016ABCModel 1Supplier 1246813.037037
        • Re: Group By Load Script With Formula
          Sunny Talwar

          Is Month a field calculated in script? It may look Jan-16, but may include different dates for different rows.

           

          I would try this:

           

          secondTable:

          LOAD

          [Market],

          MonthName(Date#([Month], 'MMM-YY')) as [Month],

          [SKU],

          [Model],

          [Supplier],

          Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3])  as [Total Inv],

          Sum([Sales]) as [Total Sales],

          Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3]) / Sum([Sales] as [Inventory Turns]

          Resident firstTable

          Group by [Market], MonthName(Date#([Month], 'MMM-YY')), [SKU], [Model], [Supplier];

          • Re: Group By Load Script With Formula
            Tamil Nagaraj

            Hi Riley,

             

            Check the below script

             

            FirstTable:
            Load *, MonthStart(Date#(Day,'DD/MM/YYYY')) as MonthStart;
            LOAD * INLINE [
                Market, Month, Day, SKU, Model, Supplier, Inv1, Inv2, Inv3, Sales
                Canada, Jan-16, 01/01/2016, ABC, Model 1, Supplier 1, 18, 19, 17, 18
                Canada, Jan-16, 02/01/2016, ABC, Model 1, Supplier 1, 13, 12, 17, 17
                Canada, Jan-16, 03/01/2016, ABC, Model 1, Supplier 1, 11, 17, 15, 11
                Canada, Jan-16, 04/01/2016, ABC, Model 1, Supplier 1, 19, 20, 15, 15
                Canada, Jan-16, 05/01/2016, ABC, Model 1, Supplier 1, 13, 20, 20, 20
            ];
            
            SecondTable:
            LOAD
            [Market],
            [Month],
            [MonthStart],
            [SKU],
            [Model],
            [Supplier],
            Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3])  as [Total Inv],
            Sum([Sales]) as [Total Sales],
            (Sum([Inv1]) + Sum([Inv2]) + Sum([Inv3])) / Sum([Sales]) as [Inventory Turns]
            Resident FirstTable Group by [Market], [Month], [MonthStart], [SKU], [Model], [Supplier];
            
            DROP Table FirstTable;
            
            
            

             

            Ouput:

            Capture.PNG

            • Re: Group By Load Script With Formula
              Riley MacDonald

              There was indeed a problem with the date field which was causing the duplicates. Thanks for your help!