6 Replies Latest reply: May 29, 2017 6:36 PM by Marco Wedel RSS

    make date field for each record in a range

    Ben Reed

      Hi i have a table that looks similar to this

      RevisionStart DateEnd Date
      102/05/201702/08/2017
      202/07/201702/25/2017
      32/11/201705/02/2017
      403/15/201704/01/2017
      503/30/201705/25/2017
      604/12/201705/15/2017

      in addition i have a master calendar.  What i want to do is create a table with the revision number for each date from Start Date to End Date from the above table so that it will link to my Master Calendar.  The result should look like this

      Master DateRevision
      02/05/20171
      02/06/20171
      02/07/20171
      02/07/2017

      2

      02/08/20171
      02/08/20172
      02/09/20172
      02/10/20172
      02/11/20172
      02/11/20173

      Any help here would be greatly appreciated.

        • Re: make date field for each record in a range
          Miguel del Valle

          Hi Ben,

           

          Maybe you can use interval match.

           

          Interval Match Feature/Function

           

          Regards

           

          Miguel del Valle

          • Re: make date field for each record in a range
            Sunny Talwar

            Try this with a while loop or IntervalMatch

             

            While loop

            Table:

            LOAD *,

              Date([Start Date] + IterNo() - 1) as [Master Date]

            While [Start Date] + IterNo() - 1 <= [End Date];

            LOAD * INLINE [

                Revision, Start Date, End Date

                1, 02/05/2017, 02/08/2017

                2, 02/07/2017, 02/25/2017

                3, 02/11/2017, 05/02/2017

                4, 03/15/2017, 04/01/2017

                5, 03/30/2017, 05/25/2017

                6, 04/12/2017, 05/15/2017

            ];

             

            Capture.PNG

            • Re: make date field for each record in a range
              Anil Babu

              You can use this in preceding load for that inline memory

               

              LOAD *,

                Date([Start Date] + IterNo() - 1) as [Start Date]

              While [Start Date] + IterNo() - 1 <= [End Date];


              PS - It will generate till End Date and adding each next day to Start Date

                • Re: make date field for each record in a range
                  Ben Reed

                  thank you all for the replies.  I am trying to use intervalmatch as this seems to be the cleanest and less cumbersome way to do this.  i am not able to get my desired results though. 

                   

                  Here is the script i am using:

                  REVISION:

                  LOAD * INLINE [

                      Revision, Start_Date, End_Date

                      1,2/5/2017,2/8/2017

                      2,2/7/2017,2/25/2017

                      3,2/11/2017,5/2/2017

                      4,3/15/2017,4/1/2017

                      5,3/30/2017,5/25/2017

                      6,4/12/2017,5/15/2017

                  ];

                   

                  MASTER_DATE:

                  LOAD *  INLINE [

                      MASTER_DATE

                      2/5/2017

                      2/6/2017

                      2/7/2017

                      2/8/2017

                      2/9/2017

                      2/10/2017

                      2/11/2017

                      2/12/2017

                      2/13/2017

                      2/14/2017

                      2/15/2017

                      2/16/2017

                      2/17/2017

                      2/18/2017

                      2/19/2017

                      2/20/2017

                      2/21/2017

                      2/22/2017

                      2/23/2017

                      2/24/2017

                      2/25/2017

                      2/26/2017

                      2/27/2017

                      2/28/2017

                      3/1/2017

                      3/2/2017

                      3/3/2017

                      3/4/2017

                      3/5/2017

                      3/6/2017

                      3/7/2017

                      3/8/2017

                      3/9/2017

                      3/10/2017

                      3/11/2017

                      3/12/2017

                      3/13/2017

                      3/14/2017

                      3/15/2017

                      3/16/2017

                      3/17/2017

                      3/18/2017

                      3/19/2017

                      3/20/2017

                      3/21/2017

                      3/22/2017

                      3/23/2017

                      3/24/2017

                      3/25/2017

                      3/26/2017

                      3/27/2017

                      3/28/2017

                      3/29/2017

                      3/30/2017

                      3/31/2017

                      4/1/2017

                      4/2/2017

                      4/3/2017

                      4/4/2017

                      4/5/2017

                      4/6/2017

                      4/7/2017

                      4/8/2017

                      4/9/2017

                      4/10/2017

                      4/11/2017

                      4/12/2017

                      4/13/2017

                      4/14/2017

                      4/15/2017

                      4/16/2017

                      4/17/2017

                      4/18/2017

                      4/19/2017

                      4/20/2017

                      4/21/2017

                      4/22/2017

                      4/23/2017

                      4/24/2017

                      4/25/2017

                      4/26/2017

                      4/27/2017

                      4/28/2017

                      4/29/2017

                      4/30/2017

                      5/1/2017

                      5/2/2017

                      5/3/2017

                      5/4/2017

                      5/5/2017

                      5/6/2017

                      5/7/2017

                      5/8/2017

                      5/9/2017

                      5/10/2017

                      5/11/2017

                      5/12/2017

                      5/13/2017

                      5/14/2017

                      5/15/2017

                      5/16/2017

                      5/17/2017

                  ];

                   

                  IntervalMatch(MASTER_DATE)

                  load distinct Start_Date, End_Date

                  Resident REVISION;

                   

                  And my results below:

                   

                  IntervalMatch.jpg