topic Re: Re: creating 30min range in QlikView

creating 30min range

Thu, 09 Oct 2014 18:24:44 GMT

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?

Re: creating 30min range

MarcoWedel — Thu, 09 Oct 2014 18:28:29 GMT

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

Re: creating 30min range

Thu, 09 Oct 2014 18:31:15 GMT

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

Re: creating 30min range

maxgro — Thu, 09 Oct 2014 18:40:59 GMT

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

load

'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

....

Re: creating 30min range

Thu, 09 Oct 2014 18:47:23 GMT

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

Re: creating 30min range

Vegar — Thu, 09 Oct 2014 18:49:50 GMT

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

];

Re: Re: creating 30min range

maxgro — Thu, 09 Oct 2014 18:57:17 GMT

Re: Re: creating 30min range

MarcoWedel — Thu, 09 Oct 2014 20:33:50 GMT

Hi,

is this what you are looking for?:

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

MarcoWedel — Thu, 09 Oct 2014 20:52:21 GMT

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

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

MarcoWedel — Thu, 09 Oct 2014 21:15:55 GMT

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

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

Re: creating 30min range

Fri, 10 Oct 2014 19:02:16 GMT

Thanks Marco. This was very helpful.

Re: creating 30min range

MarcoWedel — Fri, 10 Oct 2014 19:15:23 GMT

you're welcome

regards

Marco