Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mikevmalta
New Contributor III

Intervalmatch end not matching

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

1 Solution

Accepted Solutions

Re: Intervalmatch end not matching

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

3 Replies

Re: Intervalmatch end not matching

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

MVP
MVP

Re: Intervalmatch end not matching

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mikevmalta
New Contributor III

Re: Intervalmatch end not matching

Hi settu_periasamy and jonathan,


Thank you both for your replies. Worked great!

Kind Regards

Michael