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: 
Not applicable

Splitting 1 Column into 2 column then convert to time

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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$);

View solution in original post

3 Replies
swuehl
MVP
MVP

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$);

Not applicable
Author

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

swuehl
MVP
MVP

Here you are.