3 Replies Latest reply: Jun 29, 2016 10:12 AM by Sunny Talwar RSS

    Using Dates

    Christopher Ellis

      Hi,

       

      Could anyone please advise me on the following;

       

      I am trying to update a table using information from another, in the example below could anyone explain how this could be done.

       

      Table 1

      Date
      10 December 2015
      15 January 2016

       

      Table 2

      Start DateEnd DateCategory
      15 December 201514 January 2016Cat 1
      15 January 201620 February 2016Cat 2

       

      Table 1 would be update with the Category from table 2 where the date in table 1 falls between the Start Date and End Date of table 2

       

      DateCategory
      10 December 2015Cat 1
      15 January 2016Cat 2

       

      Many Thanks

       

      Chris

        • Re: Using Dates
          Sunny Talwar

          10 December 2015 doesn't fall between either of the two rows in table2. Is there a reason it got assigned Category Cat 1?

           

          I tried like this:

           

          Table:

          LOAD * INLINE [

              Date

              10 December 2015

              15 January 2016

          ];

           

          Table2:

          LOAD * INLINE [

              Start Date, End Date, Category

              15 December 2015, 14 January 2016, Cat 1

              15 January 2016, 20 February 2016, Cat 2

          ];

           

          Left Join (Table)

          IntervalMatch(Date)

          LOAD [Start Date],

            [End Date]

          Resident Table2;

           

          Left Join (Table)

          LOAD *

          Resident Table2;

           

          DROP Table Table2;

          DROP Fields [Start Date], [End Date];

           

          And got this:

          Capture.PNG

           

          Read about IntervalMatch here: IntervalMatch

            • Re: Using Dates
              Christopher Ellis

              Hi Sunny,

               

              Thanks for the example think I have got it now, the dates needed formatting correctly and using your example has helped to construct the solution I need (see below).

               

              Many Thanks

               

              Chris

               

               

              Table
              LOAD

              Date#("date",'DD MMMM YYYY') As "date"

              FROM [lib://Data/DataSet.txt]
              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

               

              Table1
              LOAD

              Category,
              Date#([Start date], 'DD MMMM YYYY') AS TStart,
              Date#([End date], 'DD MMMM YYYY') AS TEnd

              FROM [lib://Data/DataSet1.txt]
              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

               

              Left Join (Table)
              IntervalMatch([date])
              LOAD [TStart],
                [TEnd]
              Resident Table1;

               

              Left Join (Table)LOAD *
              Resident Table1;

               

              DROP Table Table1;
              DROP Fields [TStart],[TEnd];