topic Re: Divide Time into time slots/ time range? in QlikView

Divide Time into time slots/ time range?

Vidit — Thu, 18 Jun 2015 17:07:22 GMT

Hi,

i have the following table and i need a timeslot column.

Table:

Trans Number	Time
1	9:00
2	9:15
9	8:17
11	10:22
18	12:00
20	9:01
3	10:01
4	8:09

output Required:

Trans Number	Time	Time Slot
1	9:00	9:00-9:30
2	9:15	9:00-9:30
9	8:17	8:00-8:30
11	10:22	10:00-10:30
18	12:00	12:00-12:30
20	9:01	9:00-9:30
3	10:31	10:30-11:00
4	8:59	8:30-9:00

Kindly suggest.

Re: Divide Time into time slots/ time range?

Gysbert_Wassenaar — Thu, 18 Jun 2015 17:33:14 GMT

Something like this:

Let vEpsilon = Pow(2,-27);

LOAD

[Trans Number],

Time,

Time(floor(Time + $(vEpsilon),1/48),'h:mm') & '-' & Time(ceil(Time + $(vEpsilon),1/48),'h:mm') as [Time Slot]

FROM ...

Re: Divide Time into time slots/ time range?

MarcoWedel — Thu, 18 Jun 2015 22:28:00 GMT

Hi,

assuming that the two last rows in your output tables include typos one solution could be:

tabTransactTimes:
LOAD @1 as [Trans Number],
    @2 as Time,
    Dual(Time(SubField(Class(@2,'00:30'),'<=',1),'hh:mm')&'-'&Time(SubField(Class(@2,'00:30'),'x <',2),'hh:mm'),Class(@2,'00:30')) as [Time Slot]
FROM [https://community.qlik.com/thread/169010] (html, codepage is 1252, no labels, table is @1, filters(Remove(Row, Pos(Top, 1))));

hope this helps

regards

Marco

Re: Divide Time into time slots/ time range?

MarcoWedel — Fri, 19 Jun 2015 09:34:09 GMT

Please close your thread if your question is answered.

Thanks

Regards

Marco

Re: Divide Time into time slots/ time range?

thirumalaivadiv — Sat, 05 Jan 2019 04:46:53 GMT

Hi MArco,

I tried similar code for the 2hrs interval for timeslot it dint seem to work.And the input field is DateTime.

Can you help me how to acheive the same.Attached is the change in interval

Re: Divide Time into time slots/ time range?

thirumalaivadiv — Sat, 05 Jan 2019 05:19:24 GMT

Hi MArco

Attach is the qvw which i tried with my file and the expectation image

Thanks

Thiru

Re: Divide Time into time slots/ time range?

thirumalaivadiv — Sat, 05 Jan 2019 05:20:18 GMT

source file attached here

Re: Divide Time into time slots/ time range?

thirumalaivadiv — Sat, 05 Jan 2019 05:21:08 GMT

qvw mised to attached in the previous thread