topic Re: Splitting 1 Column into 2 column then convert to time in QlikView

Splitting 1 Column into 2 column then convert to time

Thu, 20 Sep 2012 08:36:27 GMT

i have 1 column that need to split and then convert to time to be able to get the difference of the two.

(TimeInDaily-TimeIn)

but the problem is TimeInDaily.

any suggestion or solution for this problem

pls look the attach file.

THANKS IN ADVANCE

regards

raem.paks

Re: Splitting 1 Column into 2 column then convert to time

swuehl — Thu, 20 Sep 2012 09:31:12 GMT

Your standard time format set in the script includes seconds, so it won't parse the TimeInDaily by default (only includes 'hh:mm TT'). You can use time#() function to interprete your timecode correctly.

Then use interval() function and frac() to get the differences of your timestamps per day, maybe like

Schedule:

LOAD *,

interval(TimeInDaily - frac([Time In])) as TimeInDiff;

LOAD Emp_code,

[Time In],

Schedule,

if(not match(SubField(Schedule,'-',1), ' MANAGER','NDERTIME','FLEXI 8 HOURS SHIFT'), time#(SubField(Schedule,'-',1),'hh:mm TT') ) as TimeInDaily,

if(not match(mid(Schedule,12, 8 ),'RS SHIFT'),mid(Schedule,12,8)) as TimeOutDaily

FROM

.\Sched.xls

(biff, embedded labels, table is Sheet1$);

Re: Splitting 1 Column into 2 column then convert to time

Thu, 20 Sep 2012 09:48:32 GMT

can you pls make a .qvw so i can see the result because mine is no result.

Re: Splitting 1 Column into 2 column then convert to time

swuehl — Thu, 20 Sep 2012 10:01:12 GMT

Here you are.