3 Replies Latest reply: Jan 27, 2017 3:05 PM by Julien HAMARD RSS

    First/Last available date of the month (in script)

    Julien HAMARD

      Hi everybody,

       

      I've a calendar that contain some "available dates".

      I'm looking for a way to calculate (in the script) for each dates :

      1. the previous date
      2. the first available date of the month
      3. the last available date of the month

       

      My calendar :

      Capture2.PNG

       

      The expected result :

      Capture.PNG

       

      Example of script :

      SET TimeFormat='hh:mm:ss';
      SET DateFormat='DD.MM.YYYY';
      SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
      
      Calendar:
      LOAD * INLINE [
          myDate
          05.01.2015
          06.01.2015
          10.01.2015
          15.01.2015
          27.01.2015
          03.02.2015
          10.02.2015
          12.02.2015
          23.02.2015
          01.03.2015
          02.03.2015
          03.03.2015
          04.03.2015
          31.03.2015
      ];
      
      Join(Calendar)
      LOAD *
        ,Previous(myDate) as previousDate
      Resident Calendar
      Order by myDate asc;
      
      EXIT Script;
      

       

      Any suggestion ?

       

      Thanks in advance,

      Julien

        • Re: First/Last available date of the month (in script)
          Sunny Talwar

          Try this:

           

          Calendar:

          LOAD *,

            Month(myDate) as Month;

          LOAD * INLINE [

              myDate

              05.01.2015

              06.01.2015

              10.01.2015

              15.01.2015

              27.01.2015

              03.02.2015

              10.02.2015

              12.02.2015

              23.02.2015

              01.03.2015

              02.03.2015

              03.03.2015

              04.03.2015

              31.03.2015

          ];

           

          Join(Calendar)

          LOAD Month,

            Date(Min(myDate)) as firstOfMonth,

            Date(Max(myDate)) as lastOfMonth

          Resident Calendar

          Group By Month;

           

          Join(Calendar)

          LOAD *

            ,Previous(myDate) as previousDate

          Resident Calendar

          Order by myDate asc;

           

          EXIT Script;

            • Re: First/Last available date of the month (in script)
              Julien HAMARD

              Thanks I had the same idea when driving back home ;-)

               

              SET TimeFormat='hh:mm:ss';
              SET DateFormat='DD.MM.YYYY';
              SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
              
              Calendar:
              LOAD * INLINE [
                  myDate
                  05.01.2015
                  06.01.2015
                  10.01.2015
                  15.01.2015
                  27.01.2015
                  03.02.2015
                  10.02.2015
                  12.02.2015
                  23.02.2015
                  01.03.2015
                  02.03.2015
                  03.03.2015
                  04.03.2015
                  31.03.2015
              ];
              
              Join(Calendar)
              LOAD *
                  ,Previous(myDate)                    as previousDate
                  ,Year(myDate)&'.'&Month(myDate)        as YYYY.MM
              Resident Calendar
              Order by myDate asc;
              
              Join(Calendar)
              LOAD YYYY.MM
                  ,Date(Min(myDate))                    as firstOfMonth
                  ,Date(Max(myDate))                    as lastOfMonth
              Resident Calendar
              Group by YYYY.MM;
              
              EXIT Script;
              
            • Re: First/Last available date of the month (in script)
              Ruben Marin

              Hi Julian, maybe there is a better way but this is an option:

              Calendar:

              LOAD Date(myDate) as myDate, Month(myDate) as myDateMonth INLINE [

                  myDate

                 ....[data]

              ];

               

              //Retieving min and max by month (take care of years?)

              map_first_last:

              Mapping LOAD myDateMonth, Min(myDate)&'#'&Max(myDate)

              Resident Calendar group by myDateMonth;

               

              Join(Calendar)

              LOAD *

                ,Previous(myDate) as previousDate

                ,date(Subfield(ApplyMap('map_first_last', Month(myDate)), '#', 1)) as firstDate

                ,date(Subfield(ApplyMap('map_first_last', Month(myDate)), '#', 2)) as lastDate

              Resident Calendar

              Order by myDate asc;

               

              EXIT Script;