how to create Time Bucket with different time zone.

[Anonymous]

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.

[JustinDallas · Accepted solution]

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.

[Anonymous]

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.