Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

1 Solution

Accepted Solutions

Re: Where clause with max date in load script

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

2 Replies

Re: Where clause with max date in load script

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

Not applicable

Re: Where clause with max date in load script

Fantastic, thanks so much!

Community Browser