Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
AlphaBravo
Contributor
Contributor

Using If Statement to create Timstamp Dimension

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

3 Replies
bramvdpoel
Contributor III
Contributor III

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
Contributor III
Contributor III

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

Looks like you're comparing days not hours.

 

Thanks,

mvivanco
Contributor III
Contributor III

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_Hourhour_minHour_range
01/10/2016 00:06:0000:060_1hrs
01/10/2016 00:08:4700:080_1hrs
01/10/2016 01:33:0301:331_2hrs
01/10/2016 01:38:5401:381_2hrs
01/10/2016 02:11:0702:112_3hrs
01/10/2016 02:11:2002:112_3hrs
01/10/2016 02:27:3702:272_3hrs
01/10/2016 02:40:5402:402_3hrs
01/10/2016 02:41:0902:412_3hrs
01/10/2016 03:58:4703:583_4hrs
01/10/2016 03:58:5903:583_4hrs
01/10/2016 03:59:0903:593_4hrs
01/10/2016 03:59:2003:593_4hrs
01/10/2016 04:15:0404:154_5hrs
01/10/2016 04:15:1804:154_5hrs
01/10/2016 05:49:4005:495_6hrs
01/10/2016 05:49:4105:495_6hrs
01/10/2016 06:00:3406:006_7hrs
01/10/2016 06:00:4806:006_7hrs
01/10/2016 06:01:0306:016_7hrs
01/10/2016 06:01:5306:016_7hrs
01/10/2016 07:57:3207:577_8hrs
01/10/2016 07:57:4507:577_8hrs
01/10/2016 07:57:5907:577_8hrs
01/10/2016 07:58:1307:587_8hrs
01/10/2016 08:03:0508:038_9hrs
01/10/2016 08:06:3408:068_9hrs
01/10/2016 08:06:4908:068_9hrs
01/10/2016 08:07:0208:078_9hrs
01/10/2016 08:07:1208:078_9hrs
01/10/2016 09:50:1509:509_10hrs
01/10/2016 09:50:5709:509_10hrs
01/10/2016 10:00:4010:0010_11hrs
01/10/2016 10:05:5310:0510_11hrs
01/10/2016 10:07:0910:0710_11hrs
01/10/2016 11:29:5611:2911_12hrs
01/10/2016 11:30:5911:3011_12hrs
01/10/2016 11:32:5311:3211_12hrs
01/10/2016 11:33:3311:3311_12hrs
01/10/2016 12:01:2512:0112_13hrs
01/10/2016 12:01:4912:0112_13hrs
01/10/2016 13:51:2413:5113_14hrs
01/10/2016 13:51:3413:5113_14hrs
01/10/2016 13:51:4713:5113_14hrs
01/10/2016 14:00:2914:0014_15hrs
01/10/2016 14:05:3714:0514_15hrs
01/10/2016 15:40:0615:4015_16hrs
01/10/2016 15:40:1815:4015_16hrs
01/10/2016 15:40:4115:4015_16hrs

 

greetings

Moisés Vivanco