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

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

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

qlikview979 — Tue, 30 May 2017 06:13:13 GMT

Hi,

How You are getting column "CalDate"

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

ElizaF — Tue, 30 May 2017 06:23:41 GMT

Hi,

Try:

LET vToday = '$(vMaxDate)';

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

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

jonathandienst — Tue, 30 May 2017 06:27:34 GMT

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

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

qlikview979 — Tue, 30 May 2017 06:29:18 GMT

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;

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

ahaahaaha — Tue, 30 May 2017 07:08:17 GMT

Hi,

May be like this (attached file, page 5)

Regards,

Andrey

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

devarasu07 — Tue, 30 May 2017 20:15:25 GMT

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

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

jonathandienst — Wed, 31 May 2017 05:27:59 GMT

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

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

AutoGenerate 1

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