17 Replies Latest reply: Feb 21, 2017 10:37 AM by omar bensalem RSS

    creating a single interval (min date-max date) from a list of intervals depending on a situation

    omar bensalem

      Hi all,

       

      my demand is rather simple,

       

      I have an excel file :

      Capture.PNG

       

      as you can see, the id 1 has situation A from 10/01/2017 to 13/01/2017 then from 17/01/2017 to 18/01/2017
      a situation B from 14/01/2017 to 16/01/2017

       

       

       

      So I want to be able to limit the intervals to those listed above depending on the change of the situation:

       

      here is what I've did:

       

      test:

      Load

      id,

      situation,

      date(min(debut)) as MinDate,

      date(max(fin)) as MaxDate

       

       

      Group by id,situation;

       

      LOAD

        id,

          debut,

          fin,

          situation

      FROM [lib://source/source.xlsx]

      (ooxml, embedded labels, table is Feuil1);


      this was the result :


      Capture.PNG


      Now, the question is, how can I seperate 10/01 to 13/01 and 17/01 to 18/01 ?
      Thanks !