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.

Not applicable

creating 30min range

Hi folks,

I want create a time range dimension to have the following values in 30 minutes range:

7:00 AM

7:30 AM

8:00 AM

8:30 AM

until 11:30 PM

So I have a time field that shows for example

07:15 AM

3:23 PM

4:54 PM

If I select 7:15AM I should see this date under the 7:00 to 7:30am slot.

I created an inline like this but it is not working when I do the intervalmatch with the time field from my database.

LOAD * INLINE [

Start, End, Slot

07:00, 07:29, 7:00 AM

07:30, 07:59, 7:30 AM

08:00, 08:29, 8:00 AM

];

It looks like the interval match works with numbers but when I use time it doesn't work. Any suggestion of what is the formatting to use?

1 Solution

Accepted Solutions

Re: Re: creating 30min range

in order to get all time ranges of 30min length within a day, you could create those values in a seperate table:

QlikCommunity_Thread_137179_Pic4.JPG.jpg

QlikCommunity_Thread_137179_Pic3.JPG.jpg

tabData:

LOAD *,

    Dual(Time(SubField(Class([time field],1/48),' <= x < ', 1))&' - '&Time(SubField(Class([time field],1/48),' <= x < ', 2)), Class([time field],1/48)) as [time range]

Inline [

time field

07:15 AM

3:23 PM

4:54 PM

];

tabTimeRange:

LOAD Dual(Time(SubField(Class((IterNo()-1)/48,1/48),' <= x < ', 1))&' - '&Time(SubField(Class((IterNo()-1)/48,1/48),' <= x < ', 2)), Class((IterNo()-1)/48,1/48)) as [time range]

AutoGenerate 1

While IterNo()<=48;

hope this helps

regards

Marco

11 Replies

Re: creating 30min range

You can also use the Class() function in this case

Not applicable

Re: creating 30min range

Thank you, but the class() function won't work, since I want my dimension to specifically show slots of 30 minutes.

MVP
MVP

Re: creating 30min range

or you can add a column in your script (t is your field)

load

     t,

     'from ' & time(floor(Time#(t, 'hh:mm tt'),1/48), 'hh:mm') &' to ' & time(floor(Time#(t, 'hh:mm tt'),1/48) + 1/48, 'hh:mm') as slot

     ....

1.png

Not applicable

Re: creating 30min range

This is not working from me, could you post your example please?

Vegar
Valued Contributor II

Re: creating 30min range

IntervalMatch should work with time format, but you 'll need to make sure that the field you are using are time and not string. All times are fractions.

Load

Time#('Start', 'hh:mm') as Start,

Time#('End', 'hh:mm') as End,

Slot

INLINE [

Start, End, Slot

07:00, 07:29, 7:00 AM

07:30, 07:59, 7:30 AM

08:00, 08:29, 8:00 AM

];


Please ekskuse my Norglish and Swenglish typos.
MVP
MVP

Re: Re: creating 30min range

ok

Re: Re: creating 30min range

Hi,

is this what you are looking for?:

QlikCommunity_Thread_137179_Pic2.JPG.jpg

QlikCommunity_Thread_137179_Pic1.JPG.jpg

LOAD *,

    Dual(Time(SubField(Class([time field],1/48),' <= x < ', 1))&' - '&Time(SubField(Class([time field],1/48),' <= x < ', 2)), Class([time field],1/48)) as [time range]

Inline [

time field

07:15 AM

3:23 PM

4:54 PM

];

hope this helps

regards

Marco

Re: Re: creating 30min range

in order to get all time ranges of 30min length within a day, you could create those values in a seperate table:

QlikCommunity_Thread_137179_Pic4.JPG.jpg

QlikCommunity_Thread_137179_Pic3.JPG.jpg

tabData:

LOAD *,

    Dual(Time(SubField(Class([time field],1/48),' <= x < ', 1))&' - '&Time(SubField(Class([time field],1/48),' <= x < ', 2)), Class([time field],1/48)) as [time range]

Inline [

time field

07:15 AM

3:23 PM

4:54 PM

];

tabTimeRange:

LOAD Dual(Time(SubField(Class((IterNo()-1)/48,1/48),' <= x < ', 1))&' - '&Time(SubField(Class((IterNo()-1)/48,1/48),' <= x < ', 2)), Class((IterNo()-1)/48,1/48)) as [time range]

AutoGenerate 1

While IterNo()<=48;

hope this helps

regards

Marco

Re: Re: creating 30min range

another solution using intervalmatch() instead of class() could be:

QlikCommunity_Thread_137179_Pic5.JPG.jpg

tabData:

LOAD * Inline [

time field

07:15 AM

3:23 PM

4:54 PM

];

tabTimeRange:

LOAD *,

    Dual(Start&' - '&End, Start) as [time range];

LOAD Time((IterNo()-1)/48) as Start,

    Time(IterNo()/48-1/86400000) as End

AutoGenerate 1

While IterNo()<=48;

tabLink:

IntervalMatch ([time field])

LOAD Start, End

Resident tabTimeRange;

hope this helps

regards

Marco