Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
devarasu07
Honored Contributor II

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

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

1 Solution

Accepted Solutions
MVP
MVP

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

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


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

AutoGenerate 1

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
7 Replies
qlikview979
Valued Contributor

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

Hi,

How You are getting column "CalDate"

ElizaF
Contributor II

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

Hi,

Try:

LET vToday = '$(vMaxDate)';

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

MVP
MVP

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

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);

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
qlikview979
Valued Contributor

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

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
Honored Contributor

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

Hi,

May be like this (attached file, page 5)

Regards,

Andrey

devarasu07
Honored Contributor II

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

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

Capture.JPG

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));

MVP
MVP

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

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


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

AutoGenerate 1

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein