9 Replies Latest reply: May 31, 2018 1:40 AM by ishan Bhatt RSS

    Date Generation within time range

    sindhu ja

      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.

        • Re: Date Generation within time range
          Anil Samineni

          How you want to generate? By every hour / minute / Second bases??

            • Re: Date Generation within time range
              sindhu ja

              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.

               

               

              Date Issue.PNG

                • Re: Date Generation within time range
                  A.M. van Keep

                  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,

                    • Re: Date Generation within time range
                      sindhu ja

                      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 .

                        • Re: Date Generation within time range
                          A.M. van Keep

                          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

                            • Re: Date Generation within time range
                              sindhu ja

                              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

                                • Re: Date Generation within time range
                                  A.M. van Keep

                                  Just to exclude this option: did you try name the "Created" field: custom_LinkDate?

                                   

                                  Could you send a print screen of the datamodel? Maybe the calender is linked to field "Date" and when you select 4/3/2018 it only shows the custom_LinkDates from 4/3/2018 and of course the updated "created" field.

                      • Re: Date Generation within time range
                        A.M. van Keep

                        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));

                        • Re: Date Generation within time range
                          ishan Bhatt

                          Hi Sindhu,

                           

                          Please use below logic in your script. Change the vStart and vEnd date format as per your need.

                           

                          Let vstart = YearStart(Addyears(Today(),-1));

                          let vEnd  = Today();


                          for i = '$(vstart)' to '$(vEnd)'

                          DateTable:

                          Load

                          Rowno() as ID,

                          date($(i))  as Date

                          autogenerate 1;

                          next i