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);
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.
What is you MinDate and MaxDate?
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
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);
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
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;
Can you upload sample qvw?
see my post and put date like in post
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;
This appears to be down to your number format change that you should be fine