Using If Statement to create Timstamp Dimension

[AlphaBravo]

Hello all,

I have created a times field in 'hh:mm' format. Now I want to further create a dimension that will group the time based on the range below.

What I want to achieve

=if(Date >= 00:00 and Date <= 01:00, '0-1hrs',
if(Date  > 01:00 and Date<=02:00, '1-2hrs',
if(Date  > 02:00 and Date <=04:00, ' 2 - 4hrs',

if(Date >04:00 and Date <= 06:00, '> 4 <= 6hrs',
'>6hrs'
)))).

I tried the code below but it giving me interval of 1 day instead of hours

=if(Date >= 00 and Date <= 01, '0-1hrs',
if(Date  > 01 and Date<=02, '1-2hrs',
if(Date  > 02 and Date <=04, ' 2 - 4hrs',

if(Date >04 and Date <= 06, '> 4 <= 6hrs',
'>6hrs'
)))).

Kindly help if you can.

Many Thanks

K

[bramvdpoel]

Hi,

Don’t know for sure, nut shouldn’t you use the time format instead of the date format?

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Forma...

[patilamay]

What does date field contain. can you share sample app or data.

Looks like you're comparing days not hours.

Thanks,

[mvivanco]

Hi
The solution depends on the source information and the orientation of the results obtained.

eg. I have an event occurrence list,

One of the ways to solve your requirement is as follows:

Events:
LOAD Id_event,
            Date_hour,
           text(hour(Date_hour)) & '_' & text(hour(Date_hour)+1) & 'hrs' as Hour_range,
          Timestamp(Date_hour,'hh:mm') AS hour_min
FROM
Events_a.csv;

Then the results obtained:

Date_Hour	hour_min	Hour_range
01/10/2016 00:06:00	00:06	0_1hrs
01/10/2016 00:08:47	00:08	0_1hrs
01/10/2016 01:33:03	01:33	1_2hrs
01/10/2016 01:38:54	01:38	1_2hrs
01/10/2016 02:11:07	02:11	2_3hrs
01/10/2016 02:11:20	02:11	2_3hrs
01/10/2016 02:27:37	02:27	2_3hrs
01/10/2016 02:40:54	02:40	2_3hrs
01/10/2016 02:41:09	02:41	2_3hrs
01/10/2016 03:58:47	03:58	3_4hrs
01/10/2016 03:58:59	03:58	3_4hrs
01/10/2016 03:59:09	03:59	3_4hrs
01/10/2016 03:59:20	03:59	3_4hrs
01/10/2016 04:15:04	04:15	4_5hrs
01/10/2016 04:15:18	04:15	4_5hrs
01/10/2016 05:49:40	05:49	5_6hrs
01/10/2016 05:49:41	05:49	5_6hrs
01/10/2016 06:00:34	06:00	6_7hrs
01/10/2016 06:00:48	06:00	6_7hrs
01/10/2016 06:01:03	06:01	6_7hrs
01/10/2016 06:01:53	06:01	6_7hrs
01/10/2016 07:57:32	07:57	7_8hrs
01/10/2016 07:57:45	07:57	7_8hrs
01/10/2016 07:57:59	07:57	7_8hrs
01/10/2016 07:58:13	07:58	7_8hrs
01/10/2016 08:03:05	08:03	8_9hrs
01/10/2016 08:06:34	08:06	8_9hrs
01/10/2016 08:06:49	08:06	8_9hrs
01/10/2016 08:07:02	08:07	8_9hrs
01/10/2016 08:07:12	08:07	8_9hrs
01/10/2016 09:50:15	09:50	9_10hrs
01/10/2016 09:50:57	09:50	9_10hrs
01/10/2016 10:00:40	10:00	10_11hrs
01/10/2016 10:05:53	10:05	10_11hrs
01/10/2016 10:07:09	10:07	10_11hrs
01/10/2016 11:29:56	11:29	11_12hrs
01/10/2016 11:30:59	11:30	11_12hrs
01/10/2016 11:32:53	11:32	11_12hrs
01/10/2016 11:33:33	11:33	11_12hrs
01/10/2016 12:01:25	12:01	12_13hrs
01/10/2016 12:01:49	12:01	12_13hrs
01/10/2016 13:51:24	13:51	13_14hrs
01/10/2016 13:51:34	13:51	13_14hrs
01/10/2016 13:51:47	13:51	13_14hrs
01/10/2016 14:00:29	14:00	14_15hrs
01/10/2016 14:05:37	14:05	14_15hrs
01/10/2016 15:40:06	15:40	15_16hrs
01/10/2016 15:40:18	15:40	15_16hrs
01/10/2016 15:40:41	15:40	15_16hrs

greetings

Moisés Vivanco