Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor II

ConvertTimetoLocal help

I have a table which displays different pick and pack times for individual operators for three different warehouses.

All times are in CET time. I would like to convert the time to the local time depending on which warehouse ie 001 - Kaiserslautern stays as CET, no change,  002 - Telford to GMT and 003 - Morroco to Casablanca

I have used the statement

If (WM_Direct_Productivity_Tracking.COMPANY='003',

      ConvertToLocalTime(Timestamp(Time(WM_Direct_Productivity_Tracking.END_TIME),'Casablanca'))

I'm trying to convert several time stamps eg Creation timestamp, begin and end timestamps, but can't get my head around the If statement.

I have attached my document is that helps.

Message was edited by: Mina Patel

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: ConvertTimetoLocal help

Hi,

one solution could be:

QlikCommunity_Thread_305191_Pic1.JPG

mapWarehouseTimeZone:

Mapping

LOAD * Inline [

Warehouse, TimeZone

KL, GMT+01:00

MO, Casablanca

TE, GMT+00:00

];

LOAD  ConvertToLocalTime(WM_Direct_Productivity_Tracking.END_DATE+WM_Direct_Productivity_Tracking.END_TIME-'01:00:00',ApplyMap('mapWarehouseTimeZone',Warehouse)) as WM_Direct_Productivity_Tracking.LOCAL_END_DATE_TIME

FROM YourSource

hope this helps

regards

Marco

View solution in original post

6 Replies
Highlighted
Honored Contributor II

Re: ConvertTimetoLocal help

if(Company='003,

     your convert for Casablanca,

     if(Company = '002',

          your convert for GMT,

          your convert for CET))

Highlighted
Contributor II

Re: ConvertTimetoLocal help

Thanks M W, I can get it to work for one field but how do I incorporate the other fields too.

eg begin and end time?

should I be doing a preceding load first?

sorry not getting it.

Highlighted

Re: ConvertTimetoLocal help

Hi,

one solution could be:

QlikCommunity_Thread_305191_Pic1.JPG

mapWarehouseTimeZone:

Mapping

LOAD * Inline [

Warehouse, TimeZone

KL, GMT+01:00

MO, Casablanca

TE, GMT+00:00

];

LOAD  ConvertToLocalTime(WM_Direct_Productivity_Tracking.END_DATE+WM_Direct_Productivity_Tracking.END_TIME-'01:00:00',ApplyMap('mapWarehouseTimeZone',Warehouse)) as WM_Direct_Productivity_Tracking.LOCAL_END_DATE_TIME

FROM YourSource

hope this helps

regards

Marco

View solution in original post

Highlighted
Contributor II

Re: ConvertTimetoLocal help

Hi Marco,

Sorry I'm getting confused so I applymap to my warehouse and return the converted times, for my begin and end dates?

Also I know that my system date is set to CET so KL would stay the same, TE would be backward one hour?


Confused.com.

Highlighted
Contributor II

Re: ConvertTimetoLocal help

Hi Marco,

Having looked at your sample qvw, it makes sense now and given the correct local time for each warehouse.

thank your for you time, much appreciated.

Highlighted
Contributor II

Re: ConvertTimetoLocal help

H. Marco,

I tried to add the shift patterns and interval match the local start and end times but something is not right. could you possibly direct me please.