Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikmpate0
Creator II
Creator 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

1 Solution

Accepted Solutions
MarcoWedel

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
m_woolf
Master II
Master II

if(Company='003,

     your convert for Casablanca,

     if(Company = '002',

          your convert for GMT,

          your convert for CET))

qlikmpate0
Creator II
Creator II
Author

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.

MarcoWedel

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

qlikmpate0
Creator II
Creator II
Author

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.

qlikmpate0
Creator II
Creator II
Author

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.

qlikmpate0
Creator II
Creator II
Author

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.