Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
How to generate date within specific time range in master calendar.
If suppose time range is yesterday 5pm to today 5pm (03/04/2018 17:00:01 to 04/04/2018 17:00:00)
Can anyone help me to generate the date ??
Thanks in advance.Let me know for more details.
How you want to generate? By every hour / minute / Second bases??
For hours:
LOAD
TIMESTAMP(TIMESTAMP('03/04/2018 17:00:01')+(RECNO()/24)) AS Datetime
AutoGenerate(FLOOR(TIMESTAMP('2018/04/04 17:00:00')*24)-FLOOR(TIMESTAMP('2018/04/03 17:00:01')*24));
Per minute:
LOAD
TIMESTAMP(TIMESTAMP('03-04-2018 17:00:00')+(RECNO()/(24*60))) AS Datetime
AutoGenerate(FLOOR(TIMESTAMP('2018-04-04 17:00:00')*24*60)-FLOOR(TIMESTAMP('2018-04-03 17:00:01')*24*60));
Per second:
LOAD
TIMESTAMP(TIMESTAMP('03-04-2018 17:00:00')+(RECNO()/(24*60*60))) AS Datetime
AutoGenerate(FLOOR(TIMESTAMP('2018-04-04 17:00:00')*24*60*60)-FLOOR(TIMESTAMP('2018-04-03 17:00:01')*24*60*60));
Hi Anil,
please refer my attachment.
Date and Time field was already in database.
The "created" field which i have created by using below script.
If( WeekDay(Date)<>'Sun',
if(hour(Time)>=17,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY')),
if(hour(Time)>=13,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY'))
)as Created,
The "created" Field should be linked with master calendar.
But when i link with Master Link Date , it is showing null.
It is not generating the Date.
Thanks for your reply.
But i want create for all dates .
Also for sunday , the time ended with 1 PM.
Remaining days 5PM to 5PM,
Please see my screen shot and previous reply
Try
DAYSTART(If( WeekDay(Date)<>'Sun',
if(hour(Time)>=17,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY')),
if(hour(Time)>=13,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY'))
)) as Created,
Hi Van,
My requirement is, I want to auto Generate the date in master Calendar.
"
DAYSTART(If( WeekDay(Date)<>'Sun',
if(hour(Time)>=17,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY')),
if(hour(Time)>=13,date(Date+1,'DD/MM/YYYY'),date(Date,'DD/MM/YYYY'))
)) as Created,
"
I have done this one in the table itself..this "Created"field--->Need to link with master Calendar .
What script did u use to generate the master calender? Is the master calender only based on the existing dates in the dataset?
U could generate a calender with this script for example:
FOR y = 2014 TO YEAR(TODAY())
LET vStartDate = DATE(DAYSTART(MAKEDATE($(y),1,1)),'YYYY-MM-DD');
LET vEndDate = DATE(DAYSTART(YEAREND(MAKEDATE($(y),1,1))),'YYYY-MM-DD');
temp01_Calendar:
LOAD
DATE('$(vStartDate)' + RECNO()-1,'YYYY-MM-DD') AS Date
AUTOGENERATE((vEndDate-vStartDate)+1);
NEXT y
The below script i have used to generate ,
LET varMinDate = Num(MakeDate(2015,01,01));
LET varMaxDate = Floor(MonthEnd(Today()));
LET varToday = Date(Today(), 'MM/DD/YYYY');
LET varYesterday = Date(Today()-1, 'MM/DD/YYYY');
LET varCurrentMonth = Date(Monthstart(Today()-1), 'MMM-YYYY');
TempCalendar:
LOAD
$(varMinDate)+Iterno()-1 AS Num,
Date($(varMinDate)+Iterno()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= num(today());
CustomCalendar:
LOAD
Date(TempDate,'DD/MM/YYYY') as custom_LinkDate
resident TempCalendar;
So , I have to link the "Created" field with "custom_LinkDate".
It should be autogenerate the date within specific time range