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

    Where clause with max date in load script



      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]:


        [SKU Number] as %SKU,

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

        [Week Start Date]



      (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!