Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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;
How do you suggest i cast this? Is it a function i can use to force it to the right format?
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.
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
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);
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!!!
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
It worked as expected when i transformed the Timestamp to a Date field.