Intervalmatch end not matching

[mikevmalta]

Hi,

I am using Intervalmatch to assign the time field a time bucket, ex. 13:04:59 -> 1 - 2 pm

However I am facing a situation where the time value matching the End field are not being properly mapped to a time bucket,i.e. 08:59:59 -> Null() whereas the time bucket must be 8 - 9 am. Can anyone please help me solve this issue?

Mike

[settu_periasamy · Accepted solution]

Hi,

Try to Add to Trim like below..

TimeBuckets:
LOAD
      Trim(maketime(RecNo()-1,0,0)) as Start,
      Trim(maketime(RecNo()-1,59,59)) as End,
      trim(mid(time(maketime(RecNo()-1),'h tt'),1,2)) & ' - ' & trim(time(maketime(mod(RecNo(),24)),'h tt')) as                [Time Bucket]
      AUTOGENERATE(24);

Check the Attachment..

[jonathandienst]

These are probably caused by values that are either slightly more* than (for example) 8:59:59 and therefore fall into the 1 second gap between 98:59:59 and 09:00:00. You could reduce the size of the gaps to a much smaller value:

    maketime(RecNo()-1,0,0) as Start,

    Time(maketime(RecNo() - 1, 0, 0) - 1E-9) as End,

* this could be due to very small rounding errors caused by the date/time calculations.

[mikevmalta]

Hi settu_periasamy and jonathan,

Thank you both for your replies. Worked great!

Kind Regards

Michael