topic Re: ConvertTimetoLocal help in QlikView

ConvertTimetoLocal help

qlikmpate0 — Mon, 18 Jun 2018 11:07:49 GMT

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

Re: ConvertTimetoLocal help

m_woolf — Mon, 18 Jun 2018 13:07:35 GMT

if(Company='003,

your convert for Casablanca,

if(Company = '002',

your convert for GMT,

your convert for CET))

Re: ConvertTimetoLocal help

qlikmpate0 — Tue, 19 Jun 2018 11:34:17 GMT

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.

Re: ConvertTimetoLocal help

MarcoWedel — Wed, 20 Jun 2018 00:13:40 GMT

Hi,

one solution could be:

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

Re: ConvertTimetoLocal help

qlikmpate0 — Wed, 20 Jun 2018 13:16:39 GMT

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.

Re: ConvertTimetoLocal help

qlikmpate0 — Wed, 20 Jun 2018 14:18:02 GMT

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.

Re: ConvertTimetoLocal help

qlikmpate0 — Fri, 22 Jun 2018 11:20:43 GMT

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.