Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I am trying to create a calendar , but it throws error basically it is not able to capture the date inside the variable vMinDate and vMaxDate. However if i do $(vMindate) and $(vmaxDate) it returns the date appropriately.
set vMinDate = date(min(date_va));
set vMaxDate = date(max(date_va));
test:
LOAD * INLINE [
id, date_va
1, 02/11/2015
2, 03/23/2016
3, 11/20/2016
4, 09/22/2017
5, 05/22/2012
];
tempcalendar:
load
$(vMinDate) +IterNo()-1 as strtDateNum,
$(vMinDate) +IterNo()-1 as startdate
AutoGenerate 1
while $(vMinDate) + IterNo()-1 <= $(vMaxDate);
Maybe like that:
test:
LOAD * INLINE [
id, date_va
1, 02/11/2015
2, 03/23/2016
3, 11/20/2016
4, 09/22/2017
5, 05/22/2012
];
Temp:
Load
min(date_va) as minDate,
max(date_va) as maxDate
Resident test;
Let vMinDate = Num(Peek('minDate', 0, 'Temp'));
Let vMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
tempcalendar:
load
$(vMinDate) +IterNo()-1 as strtDateNum,
$(vMinDate) +IterNo()-1 as startdate
AutoGenerate 1
while $(vMinDate) + IterNo()-1 <= $(vMaxDate);
hope this helps!
Thanks Frank
this wud work 100%..
so i was checking why cant we directly store min/max dates directly in variables(vMinDate and vMaxDate)
something like
set vMinDate = date(min(date_va));
set vMaxDate = date(max(date_va));
i could also have done this
Temp:
Load
min([Order Date]) as minDate,
max([Order Date]) as maxDate
FROM
(
TempCalendar:
LOAD
minDate + Iterno()-1 As Num,
Date(minDate + IterNo() - 1) as TempDate
Resident Temp
While minDate + IterNo() -1 <= maxDate;
would it make any difference in performance if I directly accesa fields minDate,maxDate above in TempCalendar