Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Master Calendar loop

I am trying to create a master Calendar to use within my Qlikview application. I have used the script provided by Josh Good in Creating A Master Calendar.

This is the min and max date values i get:

varMaxDate41806,475196759
varMinDate40909,0028125


This is the part that keeps looping, i haven't checked when this eventually stops(i guess when Qlikview is out of memory).

This has looped to many millions before i just cancel it. Anyone know what i am doing wrong?

TempCalendar: 

LOAD 

               $(varMinDate) + IterNo()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Tags (3)
1 Solution

Accepted Solutions
Not applicable

Re: Master Calendar loop

Make sure your date format is correct from your source table

eg like:

DATE(FLOOR(Timestamp#(fileSent,'YYYY-MM-DD hh:mm:ss')),'YYYY-MM-DD') as Date_Clean

or

date(date#(fileSent),'YYYY-MM-DD') as fileSent

you date needs to be YYYY-MM-DD or whatever your format is like.

17 Replies

Re: Master Calendar loop

What is you MinDate and MaxDate?

jagannalla
Valued Contributor III

Re: Master Calendar loop

Hi,

This code is working for me. Can you please attach sample file.

LET varMaxDate =41806.475196759;
LET varMinDate=40909.0028125;

LOAD

               $(varMinDate) + IterNo()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Cheers!!
Jagan

Re: Master Calendar loop

Your code is correct instead of the variable declaration remove , from the value and put .

Try with this

LET varMaxDate= 41806.475196759;
LET varMinDate= 40909.0028125;

And then use your code

TempCalendar:

               LOAD

               $(varMinDate) + IterNo()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

chauhans85
Esteemed Contributor

Re: Master Calendar loop

only you need to change varMaxDate and VarMinDate

you can use lik below

let varMinDate=Makedate(2010,1,1);

let varMaxDate=Date( today());

hope this helps

Not applicable

Re: Master Calendar loop

I did work when i commented out this part, and "hardcoded" the min and max values. i don't really see the difference since the Tempcalendar part only uses varMinDate and varMaxDate, and when debugging they seem correctly set with the same values as when i "hardcoded" them.

//Temp: 

//Load 

//               min(FileSent) as minDate, 

//               max(FileSent) as maxDate 

//Resident filesTable; 

//   

//   

//Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

//Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

//DROP Table Temp;

Let varMinDate = 40909.0028125;

Let varMaxDate = 41806.475196759;

senpradip007
Valued Contributor III

Re: Master Calendar loop

Can you upload sample qvw?

chauhans85
Esteemed Contributor

Re: Master Calendar loop

see my post and put date like in post


Not applicable

Re: Master Calendar loop

try this:

Temp:

min(FileSent) as minDate,

max(FileSent) as maxDate

Resident filesTable;

///******Create min/max variables*********

Let vMinDate = num(peek('minDate',0, 'Temp'));

Let vMaxDate = num(peek('maxDate', 0, 'Temp'));

Drop Table Temp;

////*******Temp Calendar******

DateField:

Load

    date($(vMinDate) + RowNo() -1) as TempDate

AutoGenerate

    $(vMaxDate) - $(vMinDate) +1;

Not applicable

Re: Master Calendar loop

This appears to be down to your number format change that you should be fine

Community Browser