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

HElP: i need to calculate time spent on the system with the following start time and end time.

bv.png

7 Replies
puttemans
Specialist
Specialist

Hello Kenneth,

You need to use the INTERVAL-function (see example below 

LOAD
*,
INTERVAL (End_time -Start_time , 'D hh:mm:ss') as interval;

LOAD

@1 as Start_time,
@2 as End_time
FROM C:\Users\rfn7501\Documents\test.xls (biff, no labels, table is [Sheet1$]);

Then, if you need days and hours separately, you go on with the interval field, using left and right functions.

Regards,

Not applicable
Author

Dear Johan,

please advice on how can i use the left and right function because i need  days and time spent separately.

Thank you,

Kind Regards,

Kenneth

puttemans
Specialist
Specialist

Hi Kenneth,

There's another way that is more sure, and that is Subfield. I've entered it into the example, so you can copy.

LOAD

           *,
               Subfield (interval,' ',1) as Hour_difference,
               Subfield (interval,' ',2) as Minutes_difference;

LOAD

          *,
          INTERVAL(End_time-Start_time, 'D hh:mm:ss') as interval;
LOAD

@1 as Start_time,
@2 as End_time
FROM

C:\Users\rfn7501\Documents\test.xls(biff, no labels, table is [Sheet1$]);

What subfield does, is looking for a separator. In our case, this is a blank (' '), and then you define which part you need.

Regards,

Johan

puttemans
Specialist
Specialist

Hello Kenneth,

Did my solution help you solve your issue? If so, could you then please close this question (mark it as answered)? If not, just let me know.

Kind regards,

Johan

Not applicable
Author

Dear Johan,

im struggling on the subfield function ,I don't understand .what should I put within the ' ' (quotes).

or is there a way I can do this on the expression.

Thank you,

Kind regards,

Kenneth

puttemans
Specialist
Specialist

Hello Kenneth,

If you take a look at the interval-function, then you'll see it is scripted 'D hh:mm:ss', where between D and hh, there is a space. This space you'll find back in your interval variable as well. (e.g. '2 00:12:12')

In order to separate the days (D) and the hours/minutes/seconds (hh:mm:ss), we will now use this space.

In the subfield-statement, you look for a unique separator, in this case the space, and you use that to 'cut' your variable.

Subfield (interval, ' ', 1) as Y actually reads like : take the variable interval, look for the separator 'space' (' '), and then return the first bit of information before the separator. So, between the brackets, you put a space (1 hit on your spacebar)

To make it more visible, you could script the interval function as 'D|hh:mm:ss'. The separator is then no longer a space, but a |.  Your variable becomes e.g. 2|00:12:12. The subfield command then becomes Subfield(interval, '|',1).


Regards,

Johan

Not applicable
Author

Dear Johan,

Thank you,  i thought maybe i have to put something your help is much appreciated. 

Kind regards,

Kenneth