Qlik Community

New to Qlik Sense

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

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dilipkumard12
Creator
Creator

how to create Time Bucket with different time zone.

I have requirement from client.

We are have different Time Zones (5:00 UTC,-3:00 UTC,+5:00 UTC,4:00 UTC)

We are received data and completed date with time stamp as an IST Zone(+5:30 UTC) Within 24hours.

I need to Convert into Different Zone.

Report Should Show Delivered before 07:00 AM,Delivered between 07:00 AM and 05:00 PM and Above 05:00 PM for  Different Time Zone.

1 Solution

Accepted Solutions
JustinDallas
Specialist II
Specialist II

I've never known how to import a Timestamp  WITH timezone into Qlik.  Most of my work has concerned US East stuff.  But assuming you can get the date translated into UTC, then something like the following script should work.

SomeTable:

LOAD *,

IF( Hour(Plus5) < 7 , 1 ) AS 'Plus5Before7',

    IF( Hour(Plus5) > 7 AND Hour(Plus5) < 17 , 1 ) AS 'Plus5After7Before5'

;

LOAD *,

ConvertToLocalTime(deliveryDateUTC, 'UTC+05:00') AS 'Plus5',

    ConvertToLocalTime(deliveryDateUTC, 'UTC-03:00') AS 'Minus3'

;

LOAD * Inline

[

orderId, deliveryDateUTC

    1, 2018-02-22 09:23:33

    2, 2018-01-02 03:00:54

    3, 2017-11-04 10:13:21

    4, 2018-04-30 14:26:51

    5, 2017-10-31 08:36:57

    6, 2018-03-17 13:11:11

    7, 2018-05-02 01:31:48

    8, 2018-03-05 01:48:30

    9, 2018-05-30 11:17:07

    10, 2017-10-14 11:39:48

    11, 2018-04-23 01:43:57

    12, 2017-12-24 01:50:54

]

;

Here you can see me converting the times to the timezones of interest, and then I bucket them.  That will make Set Analysis less obnoxious.

View solution in original post

2 Replies
JustinDallas
Specialist II
Specialist II

I've never known how to import a Timestamp  WITH timezone into Qlik.  Most of my work has concerned US East stuff.  But assuming you can get the date translated into UTC, then something like the following script should work.

SomeTable:

LOAD *,

IF( Hour(Plus5) < 7 , 1 ) AS 'Plus5Before7',

    IF( Hour(Plus5) > 7 AND Hour(Plus5) < 17 , 1 ) AS 'Plus5After7Before5'

;

LOAD *,

ConvertToLocalTime(deliveryDateUTC, 'UTC+05:00') AS 'Plus5',

    ConvertToLocalTime(deliveryDateUTC, 'UTC-03:00') AS 'Minus3'

;

LOAD * Inline

[

orderId, deliveryDateUTC

    1, 2018-02-22 09:23:33

    2, 2018-01-02 03:00:54

    3, 2017-11-04 10:13:21

    4, 2018-04-30 14:26:51

    5, 2017-10-31 08:36:57

    6, 2018-03-17 13:11:11

    7, 2018-05-02 01:31:48

    8, 2018-03-05 01:48:30

    9, 2018-05-30 11:17:07

    10, 2017-10-14 11:39:48

    11, 2018-04-23 01:43:57

    12, 2017-12-24 01:50:54

]

;

Here you can see me converting the times to the timezones of interest, and then I bucket them.  That will make Set Analysis less obnoxious.

View solution in original post

dilipkumard12
Creator
Creator
Author

Thank You For Immediate Reply Justin Dallas.

I hope this will work fine.

I need to Check the Same day of received date and completed date.