Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
devarasu07
Master II
Master 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
jonathandienst
Partner - Champion III
Partner - Champion III

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

View solution in original post

7 Replies
qlikview979
Specialist
Specialist

Hi,

How You are getting column "CalDate"

ElizaF
Creator II
Creator II

Hi,

Try:

LET vToday = '$(vMaxDate)';

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
Specialist
Specialist

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
Partner - Master
Partner - Master

Hi,

May be like this (attached file, page 5)

Regards,

Andrey

devarasu07
Master II
Master II
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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