2 Replies Latest reply: Jul 13, 2012 6:29 AM by Robert Hutchings RSS

    IntervalMatch based on TIMESTAMP field

    Dalton Ruer

      I'm having problems making Intervalmatch to work for certain values of fields that are timestamps. I'm using the sample code below and it seems that the interval match gets lost on more than 4 decimal points for the value it is trying to match. So it doesn't like certain times like 7:00:00 AM because it goes out to like 9 decimal places. However, if you change the DateOpened to a time like 6 AM instead, it works like a charm. [End goal is to map how many staff are working for each hour of each day. Real Cases table uses employee number and start/stop date and time.]

       

      LET vDateMin = Num(MakeDate(2009,1,1));
      LET vDateMax = Floor(MonthEnd(Today()));
       
      TempCalendar:
      LOAD  $(vDateMin) + RowNo() - 1 AS DateNumber, 
      Date($(vDateMin) + RowNo() - 1) AS TempDate
      AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

       

      TimeStampCalendar:
      LOAD Timestamp(DateNumber+NUM(MAKETIME(IterNo()-1, 0,0))) as TimestampField
      Resident TempCalendar
      while IterNo() <= 24;

      Cases:
      LOAD * INLINE [ CaseNumber, DateOpened, DateClosed
      0001,'1/1/2009 07:00:01 AM', '1/1/2009 9:32:00 AM',   
      0002,'1/2/2009 11:00:00 AM', '1/3/2009 6:32:00 PM'
      ]; 

       

      LEFT JOIN ([TimeStampCalendar]) INTERVALMATCH (TimestampField)
      LOAD     DateOpened, DateClosed
      RESIDENT [Cases];  

       

      LEFT JOIN ([TimeStampCalendar])
      LOAD CaseNumber,
           DateOpened,
           DateClosed
      RESIDENT [Cases];

        • Re: IntervalMatch based on TIMESTAMP field
          Robert Hutchings

          I had the same problem

           

          For some reason interval match (the time field) sometimes does a match and other times does not as shown below

           

          It seems it does not work as it should for date and time values

           

           

           

           

          EmployeeStartTimeCompleteTimeTime
          00206/07/2012 08:12:2806/07/2012 08:35:2806/07/12 08:15:00
          00206/07/2012 08:12:2806/07/2012 08:35:2806/07/12 08:20:00
          00206/07/2012 08:12:2806/07/2012 08:35:2806/07/12 08:25:00
          00206/07/2012 08:12:2806/07/2012 08:35:2806/07/12 08:30:00
          00206/07/2012 08:12:2806/07/2012 08:35:2806/07/12 08:35:00
          00206/07/2012 09:00:0006/07/2012 09:45:00-
          00206/07/2012 09:45:0006/07/2012 10:24:00-
          00206/07/2012 10:55:0506/07/2012 11:08:0506/07/12 11:00:00
          00206/07/2012 10:55:0506/07/2012 11:08:0506/07/12 11:05:00
          00206/07/2012 12:13:0106/07/2012 12:46:0306/07/12 12:15:00
          00206/07/2012 12:13:0106/07/2012 12:46:0306/07/12 12:20:00
          03806/07/2012 08:30:0006/07/2012 08:45:0006/07/12 08:40:00
          03806/07/2012 08:45:0006/07/2012 09:00:0006/07/12 08:45:00
          03806/07/2012 08:45:0006/07/2012 09:00:0006/07/12 08:50:00
          03806/07/2012 08:45:0006/07/2012 09:00:0006/07/12 08:55:00
          03806/07/2012 09:00:0006/07/2012 09:30:0006/07/12 09:00:00
          03806/07/2012 09:00:0006/07/2012 09:30:0006/07/12 09:05:00
          03806/07/2012 09:00:0006/07/2012 09:30:0006/07/12 09:10:00
            • Re: IntervalMatch based on TIMESTAMP field
              Robert Hutchings

              But this alternative approach seems to work

               

              Thanks to this excellent post by Orka

               

              http://community.qlik.com/thread/23161

               

              I used a modified version of my example below at work and it seems to work as it should

               

              ie I had to use where to limit the period etc

               

               

               

              SET ThousandSep=',';

              SET DecimalSep='.';

              SET MoneyThousandSep=',';

              SET MoneyDecimalSep='.';

              SET MoneyFormat='£#,##0.00;-£#,##0.00';

              SET TimeFormat='hh:mm:ss';

              SET DateFormat='M/D/YYYY';

              SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

              SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

              SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

               

               

               

               

              WorkTime:

              Load * Inline [

              EmployeeId, StartDate, EndDate

              1,01/01/2009 06:00:00, 01/01/2009 06:25:00

              1,01/01/2009 07:00:00, 01/01/2009 07:55:00

              1,01/01/2009 07:45:00, 01/01/2009 07:50:00

              2,01/01/2009 07:18:00, 01/01/2009 07:55:00

              3,01/01/2009 07:25:00, 01/01/2009 07:35:00

              3,01/01/2009 07:55:00, 01/01/2009 08:55:00

              4,01/01/2009 07:59:00, 01/01/2009 08:55:00

              ];

               

               

               

              left join (WorkTime)

              load StartDate

              ,EndDate

              ,autonumber(StartDate & '-' & EndDate) as AutoNumWkTime

              resident WorkTime;

               

              WorkTimeByMinute:

              load distinct

              AutoNumWkTime,

              StartDate + (IterNo()/24/60)   as Date

              resident WorkTime

              while StartDate + (IterNo()/24/60)   <= EndDate;

               

               

              Dates:

              load

              Date,

              Day(Date) as Day,

              Hour (Date) as Hour,

              Minute (Date) as Minute

              Resident WorkTimeByMinute;