Where clause with max date in load script

[#1]

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

[alexandros17 · Accepted solution]

Do as follows:

TMP:

LOAD min([Week Start Date]) as mini, max([Week Start Date]) as maxi esident [SKU Segmentation];

Let vMin = peek('mini',0,'TMP');

Let vMax = peek('maxi',0,'TMP');

drop table TMP;

now in your query write

....

where [Week Start Date] >= $(vMin ) AND [Week Start Date] <= $(vMax);

let me know

[#3]

Fantastic, thanks so much!