Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
MarcoWedel

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

View solution in original post

11 Replies
MarcoWedel

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

Not applicable
Author

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

maxgro
MVP
MVP

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
Author

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

Vegar
MVP
MVP

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

];


maxgro
MVP
MVP

ok

MarcoWedel

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

MarcoWedel

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

MarcoWedel

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