2 Replies Latest reply: Jan 21, 2015 12:18 PM by Melissa van Kluyve RSS

    Cumulative column in script using loop

      Hello,

       

      I am trying to create a table of products with a cumulative volume column. I have done this in my script successfully for the entire loaded data set, but now need to modify my script to account for multiple markets. The cumulative volume column should be per market, not based on overall volume.

       

      This is my script so far, though I am struggling with the correct syntax for the loop (red font below), please can anyone advise?

       

      //Load in all volume

      [SKU Segmentation-1]:

      LOAD

        [Market] as %SegmentationMarket,

        [SKU Number] as %SKU,

        sum([Actual Volume (HL) W+1]) as [SKUVolume-1],

        [Week Start Date]

      FROM

      [PlanningAnalyticsData.xlsm]

      (ooxml, embedded labels, table is [Demand - W+1])

      WHERE [Actual Volume (HL) W+1] >='0' AND ([Actual Volume (HL) W+1] <> '0' OR [Forecasted Volume (HL) W+1] <> '0')

      group by [Market], [SKU Number], [Week Start Date]

      ;

       

       

      //Define 52 week parameter

      Temp:

      LOAD max([Week Start Date])-365 as mini, max([Week Start Date]) as maxi resident [SKU Segmentation-1];

      Let vMaxWeekStartDateLessOneYear = peek('mini',0,'Temp');

      Let vMaxWeekStartDate = peek('maxi',0,'Temp');

       

      drop table Temp;

       

       

      //Total volume per market

      [Market List]:

      LOAD distinct

      %SegmentationMarket,

      sum([SKUVolume-1]) as [MostRecentYear_TotalVol_Master]

      resident [SKU Segmentation-1]

      where [Week Start Date] >= $(vMaxWeekStartDateLessOneYear) AND [Week Start Date] <= $(vMaxWeekStartDate)

      group by %SegmentationMarket;

       

       

      Market_to_TotalVol:

      MAPPING LOAD

        [%SegmentationMarket],

        [MostRecentYear_TotalVol_Master]

      resident

      [Market List];

       

       

      //Restrict volume to last 52 weeks

      [SKU Segmentation]:

      LOAD %SegmentationMarket,

        %SKU,

        sum([SKUVolume-1]) as SKUVolume,

        ApplyMap('Market_to_TotalVol',%SegmentationMarket,null()) as [MostRecentYear_TotalVol]

      resident [SKU Segmentation-1]

      where [Week Start Date] >= $(vMaxWeekStartDateLessOneYear) AND [Week Start Date] <= $(vMaxWeekStartDate)

      group by %SegmentationMarket, %SKU;

       

       

       

       

      drop table [SKU Segmentation-1];

       

       

       

       

        [MarketList]:

        load

        %SegmentationMarket

        resident [Market List];

       

        drop table [Market List];

       

        for vMarketNo = 1 to NoOfRows('MarketList')

        let vMarketName = peek(%SegmentationMarket,vMarketNo-1,'MarketList');

       

       

       

       

      //Create cumulative column for descending volume, per market

        [SKUSegmentation]:

        noconcatenate LOAD

        %SegmentationMarket,

        %SKU,

        SKUVolume,

        RangeSum(SKUVolume, Peek('CumulativeVol')) as CumulativeVol,

        MostRecentYear_TotalVol

        resident [SKU Segmentation]

        where %SegmentationMarket = '$(vMarketName)'

        order by SKUVolume desc;

       

        next vMarketNo

       

        drop table [SKU Segmentation];

        • Re: Cumulative column in script using loop
          Ruben Marin

          Hi, If you load SKUSegmentation with 'NoConcatenate' it will create a table for each different vMarketNo.

           

          Before the bucle you can create an (almost) empty inline table and concatente values in this table:

           

          SKUSegmentation: LOAD * Inline [DumbField];

           

          For...

          ...

          SKUSegmentation:

          Concatenate (SKUSegmentation)

          LOAD ...

           

           

          Also, I usually do this kind of cummulative sum without a bucle, I use a order by clause and peek() to check when a new market starts, I don't know wich one gets better performance:

           

          SKUSegmentation:

          LOAD ...

          If(Peek(%SegmentationMarket)=%SegmentationMarket, RangeSum(SKUVol...), SKUVolume) as CumulativeVol,

          ....

          order by %SegmentationMarket, SKUVolume desc;