Master Calendar loop

[#1]

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:

varMaxDate	41806,475196759
varMinDate	40909,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);

[its_anandrjs]

You can try this way also

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

TempCalendar:

               LOAD

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

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

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

DROP Table Temp;

[#3]

How do you suggest i cast this? Is it a function i can use to force it to the right format?

[#4 · Accepted solution]

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.

[#5]

The column in the database is DATETIME. This i assume qlikview would automatically format this correctly.

Or do i need to "force" the field when loading to use Timestamp value?

Can you only use Date value in the master calendar script? Not a timestamp?

This is how it is loaded: Timestamp(FileSent) AS FileSent

[jagannalla]

use date function

LET varMaxDate =	Date('41806.475196759');
LET varMinDate=	Date('40909.0028125');

LOAD

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

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

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

[#7]

No reason to use timestamp when the mastercalendar only creates datefields. So no i would not recommend timestamp along with it.

If you need the timestamp then use another alias. Best practice would be to convert it to only date. So i would recommend that you convert it to YYYY-MM-DD or what you use. Then you would not get any errors.

And dont convert the variables or min/max - Do it in your source load!!!

[#8]

Furthermore if you remove timestamp (If you dont use it) the performance will increase due too that there are less unique values in your dataset. So it can be more compressed

[#9]

It worked as expected when i transformed the Timestamp to a Date field.