Revised master calendar - store min/max date in back end script?

[devarasu07]

Hi All,

I've used below revised master calendar in my script, tried to store the min, max date value into variable using peak but it's not

working. do you have any suggestion to store value in back end script variable? Thanks.

MasterCalendar:

load

CalDate as Date,

Day(CalDate) as Day,

Week(CalDate) as Week,

Month(CalDate) as Month,

'Q'& Ceil(Month(CalDate)/3) as Quarter,

date(MonthStart(CalDate),'MM-YYYY') as MonthYear,

Year(CalDate) as Year ;

TempCalendar:

Load

Date(MinDate)+IterNo() as CalDate,

MaxDate

While MinDate+IterNo()<=MaxDate;

// To get min and max Date from Date field

Load

  min(FieldValue('Date',RecNo())) as MinDate,

  max(FieldValue('Date',RecNo())) as MaxDate

  AutoGenerate FieldValueCount('Date');

LET vMinDate = num(Peek('MinDate',0,'TempCalendar'));

LET vMaxDate = num(Peek('MaxDate',0,'TempCalendar'));

LET vToday = $(vMaxDate);

Thanks,

Deva

[qlikview979]

Hi,

How You are getting column "CalDate"

[ElizaF]

Hi,

Try:

LET vToday = '$(vMaxDate)';

You had to enclose the date variable $(vMaxDate) in single quotes.

[jonathandienst]

When you use preceding loads like this, only the fields in the top level (the first load statement) are loaded as output. The subsequent load statements are merely inputs to the preceding load statements. So the table TempCalendar and the fields MinDate and MaxDate do not exist when you execute the Peek(). You need to switch things around a little if you want to save the min and max dates into variables:

T_MinMax:

LOAD

    Min(FieldValue('Date',RecNo())) as MinDate,

    Max(FieldValue('Date',RecNo())) as MaxDate

AutoGenerate FieldValueCount('Date');

LET vMinDate = num(Peek('MinDate'));

LET vMaxDate = num(Peek('MaxDate'));

LET vToday = vMaxDate;

DROP Table T_MinMax;

MasterCalendar:

LOAD CalDate as Date,

    Day(CalDate) as Day,

    Week(CalDate) as Week,

    Month(CalDate) as Month,

    'Q' & Ceil(Month(CalDate)/3) as Quarter,

    Date(MonthStart(CalDate), 'MM-YYYY') as MonthYear,

    Year(CalDate) as Year ;

LOAD Date($(vMinDate) + IterNo()) as CalDate,

While $(vMinDate) + IterNo() <= $(vMaxDate);

[qlikview979]

Just try this,May be  it is very help full to you.

TempCalendar:

LOAD Date(AddMonths(Today(),-12)) as CalDate,    /// It will Show  2/30/2016

      Today() as Date                                                    //// It will show today date

AutoGenerate 1;

NoConcatenate

TempCalendar1:

Load

Date(CalDate)+IterNo() as CalDate,

Date

Resident TempCalendar While CalDate+IterNo()<=Date;

DROP Table TempCalendar;

LET vMinDate = Date(num(Peek('CalDate',0,'TempCalendar')));        It will Show  2/30/2016(Min date)

LET vMaxDate = Date(num(Peek('CalDate',-1,'TempCalendar')));      It will show today date(Max date)

MasterCalendar:

load

CalDate as Date1,

Day(CalDate) as Day,

Week(CalDate) as Week,

Month(CalDate) as Month,

'Q'& Ceil(Month(CalDate)/3) as Quarter,

date(MonthStart(CalDate),'MM-YYYY') as MonthYear,

Year(CalDate) as Year

Resident TempCalendar1;

DROP Table TempCalendar1;

[ahaahaaha]

Hi,

May be like this (attached file, page 5)

Regards,

Andrey

[devarasu07]

Hi Jonathan,

Thanks, i've tried your suggested order and getting below kind error.

Syntax error, missing/misplaced FROM:

LOAD Date(42736 + IterNo()) as CalDate,

While 42736 + IterNo() <= 42862

LOAD Date(42736 + IterNo()) as CalDate,

While 42736 + IterNo() <= 42862

so i changed like below and script ran without any error but unable to find calendar map at table view. can u advise me ? Tks

//LOAD Date($(vMinDate) + IterNo()) as CalDate,

//Date($(vMaxDate)) While Date($(vMinDate) + IterNo()) <= Date($(vMaxDate));

[jonathandienst · Accepted solution]

The hazard of writing code in an external editor . Try:

LOAD Date($(vMinDate) + IterNo()) as CalDate

AutoGenerate 1

While $(vMinDate) + IterNo() <= $(vMaxDate);