2 Replies Latest reply: Jan 20, 2015 7:54 AM by Melissa van Kluyve RSS

    Where clause with max date in load script

      Hello,

       

      I am loading in a table of product and volume information, then want to restrict this table by a date range. I have initially used static dates in my WHERE clause but now want to make these dynamic.

       

      The following script works:

       

      [SKU Segmentation]:

      LOAD

        [SKU Number] as %SKU,

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

        [Week Start Date]

      FROM

      [AnalyticsData.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 [SKU Number], [Week Start Date]

      ;

       

       

      [SKU Seg 2]:

      LOAD %SKU,

        sum(SKUVolume) as SKUVolume2

      resident [SKU Segmentation]

      where [Week Start Date] >= '01/01/2013' AND [Week Start Date] <= '01/01/2014'

      group by %SKU;

       

       

      drop table [SKU Segmentation];

       

      I would like to change the bolded WHERE statement above so that my minimum and maximum dates are as follows:

       

      min: = date(max([Week Start Date])-365)

      max: = date(max([Week Start Date]))

       

      I would think the WHERE clause would then be:

       

      where [Week Start Date] >= date(max([Week Start Date])-365) AND [Week Start Date] <= date(max([Week Start Date]))

       

      This doesn't seem to work when I load however, are there any ideas?

       

      Thanks very much!

       

      Mel