Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello i need to create a calendar to navigate the date contained in the tables.
Is it possible to create this using this approach:
1) Compute the max date;
2) Compute the min date;
3 Create the calendar using the autogenerate from the min date to the max date, increasing the numeric version of the date each row generation.
As regard as the step 3 i need a function to convert a Simple data in the form dd-mm-yyyy to an integer value.
Using this
load
Date($(m_rif_date_min) + RowNo()-1) As DT_RIFERIMENTO
autogenerate($(m_rif_date_max)-$(m_rif_date_min)+1);
It doesn't work.
Thanks
Marco
Hi!
AUTOGENERATE 1
WHILE $(m_rif_date_min)+IterNo()-1<= $(m_rif_date_max);
Hi,
Try this
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(m_rif_date_min) as minDate,
max(m_rif_date_max) as maxDate
Resident Orders;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
HI Marco
Try Like this
NoConcatenate
$($(m_rif_date_min)) + Iterno()-1 As Num,
Date($(m_rif_date_min) + IterNo() - 1) as DT_RIFERIMENTO
AutoGenerate 1 While $(m_rif_date_min) + IterNo() -1 <=$(m_rif_date_max);
It not properly works until it is used the $(date) + number expression.
I expect to use this form to generalize the generation of the date is it possible?
let m_rif_date_min=MakeDate(2013,1,1);
let m_rif_date_max=MakeDate(2015,1,1);
t1:
Date('$(m_rif_date_min)' + RowNo()-1) As DT_RIFERIMENTO
autogenerate('$(m_rif_date_max)'-'$(m_rif_date_min)'+1);
Is this possible in case of use of dates with this format: dd-mm-yyyy?
hi,
for auto generate of date ,it should be in numeric (date ) format.
Regards
Vimlesh
I use this script:
TEMP_DATA:
Min(makedate(Mid(DATA_PRES,7,4),Mid(DATA_PRES,4,2),Mid(DATA_PRES,1,2))) AS DATA_RIF_MIN,
Max(makedate(Mid(DATA_PRES,7,4),Mid(DATA_PRES,4,2),Mid(DATA_PRES,1,2))) AS DATA_RIF_MAX
RESIDENT
PRESENTAZIONI;
Let m_rif_date_min = Peek('DATA_RIF_MIN',0,'TEMP_DATA');
Let m_rif_date_max = Peek('DATA_RIF_MAX',0,'TEMP_DATA');
DROP TABLES TEMP_DATA;
Calendario:
//Date($(m_rif_date_min) + RowNo()-1) As DT_RIFERIMENTO
($(m_rif_date_min) + RowNo()-1) As DT_RIFERIMENTO
I need also to obtain the min and max date from different tables, so what should i change in these few lines of code:
You will change variable Name for them and Date name(through alias) only.
one thing you can do make a common date calendar after creating all date.
