topic Re: Intervalmatch end not matching in QlikView

Intervalmatch end not matching

mikevmalta — Fri, 05 Feb 2016 09:49:11 GMT

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

Re: Intervalmatch end not matching

settu_periasamy — Mon, 08 Feb 2016 03:08:19 GMT

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..

Re: Intervalmatch end not matching

jonathandienst — Mon, 08 Feb 2016 05:08:53 GMT

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.

Re: Intervalmatch end not matching

mikevmalta — Mon, 08 Feb 2016 13:08:35 GMT

Hi settu_periasamy and jonathan,

Thank you both for your replies. Worked great!

Kind Regards

Michael